COUNT number of NULLs in multiple columns

Question

I want to count the number of occurrences of NULL in several columns in a single table. I need the counts per column, not a total count on the entire table. I would very strongly prefer to do so in a single query, to avoid the overhead of the back and forth, simplify my app side code, and avoid multiple table scans.

How can I count the number of rows where each particular column is NULL?

Ideas so far

• COUNT explicit counts things that are not NULL, but I could do this to invert NULL into not-NULL and vice versa to force COUNT to do what I want:

  SELECT COUNT(CASE WHEN my_column IS NULL THEN TRUE ELSE NULL END) FROM my_table;
  

  but that's really, really ugly, especially since I want to do this on multiple columns in the same query.

• NULLIF returns NULL when the input is NULL, so that isn't helpful.

Is there a cleaner way?

Answer 1

Try this:

SELECT COUNT(*)-COUNT(column1) As column1, COUNT(*)-COUNT(column2) As column2 FROM my_table; 

Maybe this will work for you. Since like you mentioned, COUNT only counts non-null values, this should give you the sum of null values you need.