Problem solve Get help with specific problems with your technologies, process and projects.

Counting a row's NULL columns

How can I count the number of columns in a row that are not null in SQL?

How can I count the number of columns in a row that are not null?

Assuming your table looks something like this:

 id column1 column2 column3 ...
187    2       9     null
456  null     37      42
937    7      12       4

The real answer is: you shouldn't need to. The answer to the question you asked is:

select case when column1 is null
            then 0 else 1 end
      +case when column2 is null
            then 0 else 1 end
      +case when column3 is null
            then 0 else 1 end
            as not_null_columns
  from TableA
 where id = 937

Any time you find yourself wanting to do something to "all columns in a row" like this, step back and consider redesigning the table. There's a good chance the table isn't in first normal form. The main symptom is finding yourself writing clumsy queries with a lot of repetitive expressions.

A normalized one-to-many table would look something like this:

 id columnx columnvalue
187    1          2
187    2          9
456    2         37
456    3         42
937    1          7
937    2         12
937    3          4

Now the query gets a lot simpler:

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

Much nicer, wouldn't you agree?

This was last published in October 2007

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.