views:

162

answers:

5

Hello,

I'm trying to count the number of records that have a null DateTime value. However, for some reason, my attempts have failed. I have tried the following two queries without any luck:

SELECT COUNT(BirthDate) 
  FROM Person p
 WHERE p.BirthDate IS NULL

and

SELECT COUNT(BirthDate)
  FROM Person p
 WHERE p.BirthDate = NULL

What am I doing wrong? I can see records with a BirthDate of NULL when I query all of the records.

+3  A: 
SELECT COUNT(*)
FROM Person
WHERE BirthDate IS NULL
Joel Coehoorn
+2  A: 

try this

SELECT     COUNT(*) 
FROM     Person p
WHERE     p.BirthDate IS NULL
Fredou
A: 

You need to use "IS NULL" not "= NULL"

SELECT
  COUNT('')
FROM
  Person p
WHERE
  BirthDate IS NULL
MattB
why empty string?
Fredou
Using the empty string is less load on the DB because it doesn't retrieve any data, just an empty string.
MattB
But my understanding is that COUNT(*) doesn't load anything either. It just does a base count. At least in modern SQL Server instances.
Orion Adrian
@MattB: empty string, literal 1 or * all do the same thing. However, the reason you give is not valid.
gbn
+7  A: 

All answers are correct, but I'll explain why...

COUNT(column) ignores NULLs, COUNT(*) includes NULLs.

So this works...

SELECT COUNT(*)
FROM Person
WHERE BirthDate IS NULL
gbn
+1  A: 

This is happening because you are trying to do a COUNT on NULL. I think that if you check the messages tab, you may have a message there saying NULL values eliminated from aggregate

What you have to change is the field that you are counting

Select Count (1) FROM Person WHERE BirthDate IS NULL

Select Count (*) FROM Person WHERE BirthDate IS NULL

Select Count (1/0) FROM Person WHERE BirthDate IS NULL

Select Count ('duh') FROM Person WHERE BirthDate IS NULL /* some non null string*/

Raj More
Good point with 1/0. Does it work though? ;-) In EXISTS, yes, but I've not tried it like this (and can't just now)
gbn
it sure works..
Raj More
Tried it. It works. Weird.
Rob Garrison