Re: [SQL] can i make this sql query more efficiant?

[Franco Bruno Borghesi]

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 [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly

pgp00000.pgp
Description: signature