Can't count null values...

[simian336]

how come I can't count nulls

select count([copper_drop])
from Structures
where Copper_Drop is null

-----------
0
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)


I have set SET ANSI_NULLS off and SET ANSI_NULLS on
with the same result.

Thanks

Simi

 

[r937]

aggregate columns disregard NULLs

so don't try to count them, count something else instead

SELECT COUNT(1)
  FROM Structures
 WHERE Copper_Drop IS NULL

or

SELECT COUNT('foo')
  FROM Structures
 WHERE Copper_Drop IS NULL

or

SELECT COUNT(*)
  FROM Structures
 WHERE Copper_Drop IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

All aggregates ignore nulls. So... you could do this:

Select Count(1) As NullCount
From   Structures
Where  Copper_Drop Is NULL

You cal also get other useful information like:

Select Count(1) As NullCount,
       Count(Copper_Drop) As NonNullCount,
       100.0 * Count(1) / Count(Copper_Drop) As NullPercent
From   Structures

Note: The number 1 here could be anything. It's a hard coded constant. The important thing to realize is that it is not NULL, so it will be included in the count. It could have been Count(999) or Count('KerFlunk').

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

sorry, typo

aggregate functions disregard nulls

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

Damn Rudy. It's like you were reading my mind and managed to type it faster than me. If I had seen your post before writing mine, I wouldn't have bothered.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Thanks to you both as always.

Simi