Thread: Possible to do this in one query?
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! John Oakes
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
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: > > 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! You need a table of failcodes first: CREATE TABLE failcodes ( 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 SELECT count (*) FROM tablenameGROUP BY failcode ); Or, be a sneaky bastard and add a fake record for each failcode, and then count(*) - 1. :) -- 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 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me