From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: Robert Treat <xzilla@users.sourceforge.net>
To: pgsql-sql@postgresql.org
Subject: Re: can i make this sql query more efficiant?
Date: Thu, 3 Apr 2003 19:15:15 -0300
Message-ID: <200304031915.16517.franco@akyasociados.com.ar> (raw)
In-Reply-To: <1049403724.13799.5473.camel@camel>
References: <1049403724.13799.5473.camel@camel>
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@postgresql.org so that your
> message can get through to the mailing list cleanly
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar, xzilla@users.sourceforge.net
Subject: Re: can i make this sql query more efficiant?
In-Reply-To: <200304031915.16517.franco@akyasociados.com.ar>
* 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