From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Organization: AK y Asociados S.R.L.
To: Robert Treat <xzilla@users.sourceforge.net>,
	pgsql-sql@postgresql.org
Subject: Re: can i make this sql query more efficiant?
Date: Thu, 3 Apr 2003 19:15:15 -0300
User-Agent: KMail/1.5
References: <1049403724.13799.5473.camel@camel>
In-Reply-To: <1049403724.13799.5473.camel@camel>
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg=pgp-sha1; boundary="Boundary-02=_0JLj+LbEzho6ijt";
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200304031915.16517.franco@akyasociados.com.ar>

--Boundary-02=_0JLj+LbEzho6ijt
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: signed data
Content-Disposition: inline

if you're allowed to change the resultset structure, you could do:
SELECT
   event,
   level,=20
   count(*)
FROM
   baz
GROUP BY
   event,
   level;=20=20=20

 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=20=20=20
   EL.event,
   EL.level,=20
   count(baz.*)
FROM
   (
      SELECT DISTINCT
         B1.event, B2.level=20
      FROM=20
         baz B1=20
         CROSS JOIN baz B2
   ) EL
   LEFT JOIN baz ON (baz.event=3DEL.event AND baz.level=3DEL.level)=20=20
GROUP BY
   EL.event,
   EL.level;=20=20=20

 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 =3D 1 and a.event=3Dbaz.event) as ones,
> 	(select count(*) from baz a
> 		where level =3D 2 and a.event=3Dbaz.event) as twos,
> 	(select count(*) from baz a
> 		where level =3D 3 and a.event=3Dbaz.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

--Boundary-02=_0JLj+LbEzho6ijt
Content-Type: application/pgp-signature
Content-Description: signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+jLJ0z/PwDAvtu7oRApe3AKCV0gNktohyYBn9I2eqQZ31RDlVQACcDWnx
iJFOEWxG3uY6RCHyLbB1rAo=
=6bXU
-----END PGP SIGNATURE-----

--Boundary-02=_0JLj+LbEzho6ijt--