sql to count nulls (I think)
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. Thanks in advance Vanessa
On Wed, 21 May 2003 01:51:39 +1200, "V Bradley" <vobradley@yahoo.com>
wrote:
Seems this would work . . . . .
select <original select>
into temp temp1 with no log
;
select BayID, count(*)
from temp1
where DateRemoved is null;
>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.
>
>Thanks in advance
>
>Vanessa
>