Received: from gateway.blackrobes.net (unknown [65.169.176.14]) by postgresql.org (Postfix) with ESMTP id 722A5475914 for ; Thu, 11 Jul 2002 18:08:53 -0400 (EDT) Received: from io ([192.168.1.64]) by gateway.blackrobes.net with smtp (Exim 3.22 #1) id 17Sm7h-0004G7-00 for pgsql-sql@postgresql.org; Thu, 11 Jul 2002 17:09:17 -0500 From: Loyd Goodbar To: pgsql-sql@postgresql.org Subject: Re: SQL problem with aggregate functions. Date: Thu, 11 Jul 2002 17:08:52 -0500 Reply-To: loyd@blackrobes.net Message-ID: References: <200207091036.17913.david.bouriaud@ac-rouen.fr> <200207110837.KAA07881@rodos> In-Reply-To: <200207110837.KAA07881@rodos> X-Mailer: Forte Agent 1.8/32.553 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable X-Archive-Number: 200207/170 X-Sequence-Number: 8549 I would suggest something like select sum(case when f1 =3D 'D' then 1 else 0 end) as D_COUNT, sum(case when f1 =3D 'R' then 1 else 0 end) as R_COUNT, sum(case when f1 =3D 'X' then 1 else 0 end) as X_COUNT from tab where f1 in ('D','R','X') Not sure what the "field group" represents. HTH, Loyd On Thu, 11 Jul 2002 10:37:40 METDST, Christoph Haller wro= te: >>=20 >> I've got a table in which there is a field that can have one amongst 3= =20 >> possible values : D, R, X. Is it possible to get in one query the count = of=20 >> this different values.Please, note that I don't want to have a querry li= ke=20 >> this : >> "select count (*) from tab group by f1;", cause i want to get all the po= ssible=20 >> count values in one row (these data are already grouped on another field= ). >> To give a more accurate example, here is what I want to retrieve : >>=20 >> Field group | count of D | count of R | count of X. >>=20 >> Any clues ? >> --=20 >What about something like=20 > > >SELECT SUM(f1_d) AS count_d, > SUM(f1_r) AS count_r, > SUM(f1_x) AS count_x >FROM ( > SELECT CASE WHEN f1 =3D 'D' THEN 1 ELSE 0 END AS f1_d, > CASE WHEN f1 =3D 'R' THEN 1 ELSE 0 END AS f1_r, > CASE WHEN f1 =3D 'X' THEN 1 ELSE 0 END AS f1_x > FROM tab ) AS foo ; > >Regards, Christoph=20 > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org --=20 "Why, you can even hear yourself think." --Hobbes "This is making me nervous. Let's go in." --Calvin loyd@blackrobes.net ICQ#504581 http://www.blackrobes.net/