if you're allowed to change the resultset structure, you could do:
SELECT event, level, count(*)
FROM baz
GROUP BY event, level;
event | level | count
-------+-------+-------x | 1 | 1x | 2 | 1x | 3 | 1y | 2 | 1y |
3| 2
(5 rows)
of course it doesn't show you the rows where the count is zero.
if you need the zeros, do this
SELECT EL.event, EL.level, count(baz.*)
FROM ( SELECT DISTINCT B1.event, B2.level FROM baz B1 CROSS JOIN baz B2 ) EL LEFT
JOINbaz ON (baz.event=EL.event AND baz.level=EL.level)
GROUP BY EL.event, EL.level;
event | level | count
-------+-------+-------x | 1 | 1x | 2 | 1x | 3 | 1y | 1 | 0y |
2| 1y | 3 | 2
(6 rows)
hope it helps.
On Thursday 03 April 2003 18:02, Robert Treat wrote:
> create table baz (event text, level int);
>
> insert into baz values ('x',1);
> insert into baz values ('x',2);
> insert into baz values ('x',3);
> insert into baz values ('y',2);
> insert into baz values ('y',3);
> insert into baz values ('y',3);
>
> select * from baz;
>
> event | level
> -------+-------
> x | 1
> x | 2
> x | 3
> y | 2
> y | 3
> y | 3
> (6 rows)
>
>
> I want to know how many ones, twos, and threes there are for each event:
>
> 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)
>
>
> which is fine, but I am wondering if there is a better way to do this?
> I'd mainly like to reduce the number of subqueries involved. Another
> improvement would be to not have to explicitly query for each level,
> though this isn't as big since I know the range of levels in advance
> (famous last words for a dba :-)
>
> Thanks in advance,
>
> Robert Treat
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
