Re: can i make this sql query more efficiant?

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 | 1
x | 2 | 1
x | 3 | 1
y | 2 | 1
y | 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 JOIN baz ON (baz.event=EL.event AND baz.level=EL.level)
GROUP BY
EL.event,
EL.level;

event | level | count
-------+-------+-------
x | 1 | 1
x | 2 | 1
x | 3 | 1
y | 1 | 0
y | 2 | 1
y | 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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly