Ask The Oracle Expert: Questions & Answers

Counting NULL columns

Counting NULL columns

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

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!