Received: from xyzzy.lan.internal (cvx-west-206-47-80-67.kingston.net [206.47.80.67]) by postgresql.org (Postfix) with ESMTP id A8EEE475BB0 for ; Wed, 27 Mar 2002 09:58:01 -0500 (EST) Received: from drew by xyzzy.lan.internal with local (Exim 3.35 #1 (Debian)) id 16qEsE-000236-00; Wed, 27 Mar 2002 09:58:02 -0500 Date: Wed, 27 Mar 2002 09:58:02 -0500 To: John Oakes Cc: pgsql-sql@postgresql.org Subject: Re: Possible to do this in one query? Message-ID: <20020327145802.GA7777@xyzzy.dhs.org> References: <001301c1d4f3$37bd5160$f8832341@john> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="vkogqOf2sHV7VnPd" Content-Disposition: inline In-Reply-To: <001301c1d4f3$37bd5160$f8832341@john> User-Agent: Mutt/1.3.27i From: "Andrew G. Hammond" X-Fnord: +++ath X-WebTV-Stationery: Standard; BGColor=black; TextColor=black X-Message-Flag: Message text blocked: ANTI-MICROSOFT OPINIONS X-BeenThere: drew@xyzzy.dhs.org X-Archive-Number: 200203/357 X-Sequence-Number: 7231 --vkogqOf2sHV7VnPd Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Tue, Mar 26, 2002 at 01:22:41PM -0500, John Oakes wrote: > I have a table with a column that contains fail codes. These codes are 1= -9. > I need to get a count of each of the codes. Right now I have separate > queries: >=20 > select count(*) from tablename > where failcode =3D '1'; >=20 > If I use GROUP BY it obviously doesn't get the count for codes that don't > exist. I need to have 0 returned though so I have a listing of all 9 fail > codes and their total, even if it is zero. I tried UNION like this: >=20 > select count(*) from tablename > where failcode =3D '1'; > UNION > select count(*) from tablename > where failcode =3D '2'; >=20 > etc, but it too excludes the codes that have a count of zero. Right now I > have resorted to using 9 separate queries. Is there a way to do this with > one? Thank you! You need a table of failcodes first: CREATE TABLE failcodes (=20 failcode INTEGER PRIMARY KEY, description TEXT ); -- populate it: INSERT INTO failcodes ( 1, 'Attacked by mob of angry kittens' ); -- ... INSERT INTO failcodes ( 9, 'Wrong phase of moon' ); SELECT coalesce(count, 0) FROM ( SELECT failcode FROM failcodes NATURAL LEFT OUTER JOIN=20 SELECT count (*) FROM tablename GROUP BY failcode ); Or, be a sneaky bastard and add a fake record for each failcode, and=20 then count(*) - 1. :) --=20 Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481= =20 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me --vkogqOf2sHV7VnPd Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjyh3foACgkQCT73CrRXhLHkaACeOeu2nEJ37XtZIq2uMpfRTVfD fd0An3yEKGeOCS3TjdeixALoOCCta4bs =iS9V -----END PGP SIGNATURE----- --vkogqOf2sHV7VnPd--