Re: can i make this sql query more efficiant?

On 03 Apr 2003 16:02:04 -0500, Robert Treat
<xzilla(at)users(dot)sourceforge(dot)net> wrote:
>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 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.

SELECT event,
SUM (CASE level WHEN 1 THEN 1 ELSE 0 END) AS ones,
SUM (CASE level WHEN 2 THEN 1 ELSE 0 END) AS twos,
SUM (CASE level WHEN 3 THEN 1 ELSE 0 END) AS threes
FROM baz
GROUP BY event;

> Another
>improvement would be to not have to explicitly query for each level,

This might be a case for a clever set returning function, but that's
not my realm. Wait for Joe to jump in ;-)

Servus
Manfred