From: Manfred Koizar <mkoi-pg@aon.at>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: can i make this sql query more efficiant?
Date: Fri, 04 Apr 2003 01:13:18 +0200
Message-ID: <2afp8vsf99lhrrtr411ncivnr577s1v394@4ax.com> (raw)
In-Reply-To: <1049403724.13799.5473.camel@camel>
References: <1049403724.13799.5473.camel@camel>
On 03 Apr 2003 16:02:04 -0500, Robert Treat
<xzilla@users.sourceforge.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: mkoi-pg@aon.at, xzilla@users.sourceforge.net, pgsql-sql@postgresql.org
Subject: Re: can i make this sql query more efficiant?
In-Reply-To: <2afp8vsf99lhrrtr411ncivnr577s1v394@4ax.com>
References: <1049403724.13799.5473.camel@camel> <2afp8vsf99lhrrtr411ncivnr577s1v394@4ax.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox