Q

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!

This was first published in December 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close