PanczerTank PanczerTank - 5 months ago 22
SQL Question

Cannot directly count Nulls in SSMS

I am trying to count the number of nulls in column using COUNT and a WHERE clause. The result of this query returns counts of 0 when there are nulls in the table for Attr_620_Lookup. I could count all rows and subtract IS NOT NULL.. but is there a way to directly count nulls?

SELECT Attr_616 AS Location, COUNT(Attr_620_Lookup)
FROM dbo.AttributesDenormalized AS ad
WHERE (Attr_620_Lookup IS NULL)
GROUP BY Attr_616

Answer

When you specify an expression in COUNT it will count non-null values. Just use COUNT(*) instead:

SELECT Attr_616 AS Location, COUNT(*)
FROM dbo.AttributesDenormalized AS ad
WHERE (Attr_620_Lookup IS NULL)
GROUP BY Attr_616
Comments