<cut>
> select
> event,
> (select count(*) from baz a
> where level = 1 and a.event=baz.event) as ones,
> (select count(*) from baz a
> where level = 2 and a.event=baz.event) as twos,
> (select count(*) from baz a
> where level = 3 and a.event=baz.event) as threes
> from
> baz
> group by
> event;
>
> which gives me:
>
> event | ones | twos | threes
> -------+------+------+--------
> x | 1 | 1 | 1
> y | 0 | 1 | 2
> (2 rows)
<cut>
What about this:
select event, sum(case when level=1 then 1 else 0 end) as ones, sum(case when level=2 then 1 else 0 end) as twos,
sum(casewhen level=3 then 1 else 0 end) as threes
from baz
group by event;
Regards,
Tomasz Myrta