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



Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.