Date: Mon, 22 Jul 2002 10:26:44 +0200
From: Hubert depesz Lubaczewski <depesz@depesz.pl>
To: David BOURIAUD <david.bouriaud@ac-rouen.fr>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL problem with aggregate functions.
Message-ID: <20020722082644.GA32346@depesz.pl>
Reply-To: depesz@depesz.pl
References: <200207091036.17913.david.bouriaud@ac-rouen.fr>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="bg08WKrSYDhXBjb5"
Content-Disposition: inline
In-Reply-To: <200207091036.17913.david.bouriaud@ac-rouen.fr>
User-Agent: Mutt/1.4i

--bg08WKrSYDhXBjb5
Content-Type: text/plain; charset=iso-8859-2
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Jul 09, 2002 at 10:36:17AM +0200, David BOURIAUD wrote:
> Field group | count of D | count of R | count of X.

if you want this that way, i suggest using subselects.
like:
select
	distinct field_group,
	(select count(*) from table t2 where t2.field_group =3D t1.field_group and=
 t2.field=3D'D') as count_of_d,
	(select count(*) from table t2 where t2.field_group =3D t1.field_group and=
 t2.field=3D'R') as count_of_r,
	(select count(*) from table t2 where t2.field_group =3D t1.field_group and=
 t2.field=3D'X') as count_of_x
from
	table;

should work the way you want it.

anyway, i belive that making this:

select field_group, field, count(*) from table where field in
('D','R','X') group by field_group, field;

and then processing results in client application, should be a little
bit better/faster solution.

depesz

--=20
hubert depesz lubaczewski                          http://www.depesz.pl/
------------------------------------------------------------------------
M=F3j Bo=BFe, spraw abym milcza=B3, dop=F3ki si=EA nie upewni=EA,  =BFe  na=
prawd=EA  mam
co=B6 do powiedzenia.                                      (c) 1998 depesz


--bg08WKrSYDhXBjb5
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE9O8HEKBwKA0prOAkRAlroAJ9mRetQzq/anxtYUOkm037253L6ygCbBM7/
qJ9Nv+8v0+ujph87r6H2ne8=
=dRwz
-----END PGP SIGNATURE-----

--bg08WKrSYDhXBjb5--