Count null values using CASE WHEN

Question

I am trying to count the number of missing postal codes using CASE to flag null values as 1, otherwise 0.

 CASE WHEN ([postcode]=NULL)
 THEN 1
 ELSE 0 END AS pc_missing

Even though I know there are lots of missing data, none of them receive the value of 1.

What have I done wrong?

score 0 · Answer 1 · answered Oct 22 '14 at 16:09

null is not a value - it's the lack of a value. null returns "unknown" (which is not true) when evaluated against any value operator - =, !=, >, <, etc. Instead, you should check it with the is [not] null operator:

 CASE WHEN ([postcode] IS NULL)
 THEN 1
 ELSE 0 END AS pc_missing

Count null values using CASE WHEN

1 Answers1