Counting NULL columns

Our SQL expert explains how to count null columns in a row.

How do you count null columns in a row? I see you have a nice solution for counting not null columns in a row.

You are likely referring to the recent question Counting a row's NULL columns. (Note: that title is a bit misleading. It should actually have been Counting a row's NOT NULL columns.)

If your table is similar to the sample table in that question, then the answer is the same: you shouldn't need to write that horrendous series of CASE expressions for each column. Normalize the table, so that you have a single column with multiple rows, instead of multiple columns in one row. Then the counting becomes trivial:

select count(columnx) as not_null_columns
  from TableB
 where id = 937

Notice that this gives the non-NULL count (it's the same query from the previous question). The trick for counting NULL values—if you can call it a trick—is that the normalized table would typically not have a row for a NULL, i.e. a value that isn't there! In the unnormalized table, you can count the number of NULLs using a similarly horrendous series of CASE expressions, with IS NULL instead of IS NOT NULL. In the normalized table, there are no NULLs at all, so there is nothing to count!

Dig Deeper on Oracle and SQL