Received: from post.webmailer.de (natpost.webmailer.de [192.67.198.65]) by postgresql.org (Postfix) with ESMTP id 34C22475955 for ; Tue, 26 Mar 2002 22:09:09 -0500 (EST) Received: from there (pD9EB161A.dip.t-dialin.net [217.235.22.26]) by post.webmailer.de (8.9.3/8.8.7) with SMTP id EAA29988; Wed, 27 Mar 2002 04:09:09 +0100 (MET) Message-Id: <200203270309.EAA29988@post.webmailer.de> Content-Type: text/plain; charset="iso-2022-jp" From: Ian Barwick To: "John Oakes" , Subject: Re: Possible to do this in one query? Date: Wed, 27 Mar 2002 03:10:50 +0100 X-Mailer: KMail [version 1.3.2] References: <001301c1d4f3$37bd5160$f8832341@john> In-Reply-To: <001301c1d4f3$37bd5160$f8832341@john> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/352 X-Sequence-Number: 7226 On Tuesday 26 March 2002 19:22, 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: > > select count(*) from tablename > where failcode = '1'; > > 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: > > select count(*) from tablename > where failcode = '1'; > UNION > select count(*) from tablename > where failcode = '2'; > > 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! If you have a seperate table containing all possible fail codes you can do something like this: select fc.failcode, count(tn.failcode) from failcode_table fc left join tablename tn on tn.failcode=fc.failcode group by fc.failcode Ian Barwick