Re: sql to count nulls (I think)

Posted in 2003

Mark D. Stock — Wed, 21 May 2003 00:05:33 +0100 — source: Usenet: comp.databases.informix

V Bradley wrote: > Hi there, newbie here again. This is probably an easy one for you guru's, > but it's driving me nuts. Please help. > > I have this data (extract): > > BayID Dateloaded DateRemoved > 1 01/01/2003 02/03/2003 > 1 06/01/2003 > 1 08/01/2003 18/04/2003 > 2 12/02/2003 19/05/2003 > 2 06/05/2003 > 2 06/05/2003 > 3 01/05/2003 02/05/2003 > 3 01/05/2003 02/05/2003 > 3 02/05/2003 04/05/2003 > > I need an SQL which will show me the following results: > (No_of_packs would equate to number of fields in each bay that don't have > date removed value) > > BayID No_of_packs > 1 1 > 2 2 > 3 0 > > Have nearly got it running, but can't make it show the zero value in > No_of_packs. I guess you are looking for something like: SELECT bayid, 0 no_of_packs FROM my_table WHERE dateremoved IS NOT NULL GROUP BY 1 UNION SELECT bayid, COUNT(*) FROM my_table WHERE dateremoved IS NULL GROUP BY 1 ORDER BY 1 Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

↪ replying to Mark D. Stock

zzz — Tue, 20 May 2003 20:34:22 -0500 — source: Usenet: comp.databases.informix

On Wed, 21 May 2003 00:05:33 +0100, "Mark D. Stock" <mdstock@mydassolutions.com> > >V Bradley wrote: >> Hi there, newbie here again. This is probably an easy one for you guru's, >> but it's driving me nuts. Please help. >> >> I have this data (extract): >> >> BayID Dateloaded DateRemoved >> 1 01/01/2003 02/03/2003 >> 1 06/01/2003 >> 1 08/01/2003 18/04/2003 >> 2 12/02/2003 19/05/2003 >> 2 06/05/2003 >> 2 06/05/2003 >> 3 01/05/2003 02/05/2003 >> 3 01/05/2003 02/05/2003 >> 3 02/05/2003 04/05/2003 >> >> I need an SQL which will show me the following results: >> (No_of_packs would equate to number of fields in each bay that don't have >> date removed value) >> >> BayID No_of_packs >> 1 1 >> 2 2 >> 3 0 >> >> Have nearly got it running, but can't make it show the zero value in >> No_of_packs. > >I guess you are looking for something like: > >SELECT bayid, 0 no_of_packs >FROM my_table >WHERE dateremoved IS NOT NULL >GROUP BY 1 >UNION >SELECT bayid, COUNT(*) >FROM my_table >WHERE dateremoved IS NULL >GROUP BY 1 >ORDER BY 1> >Cheers, You're going to end up with: BayID No_of_packs 1 0 1 1 2 0 2 2 3 0 Aren't you? You could add the following condition to the first where clause to fix it: AND NOT EXISTS (SELECT 1 FROM my_table b WHERE b.bayid = my_table.bayid AND DateRemoved IS NULL) Or am I mistaken?