How do I get the count of null value columns per row in a return set?

Question

I'm looking for a query which will return me an extra column at the end of my current query which is the count of all columns within the return set which contain a null column. For example:

Col 1 - Col 2 - Col 3
A       B       0
A       NULL    1
NULL    NULL    2

Is there a simple way to get this return set based on the row values rather than having to requery all the criteria which fetches the original rows?

Answer 1

If there isnt a very good reason you need to do this in the SQL, you should just do a for loop through the result set and count the NULL vlues then.

The cost goes from n^n to n..

Answer 2

Ugly solution:

select Col1, Col2,
       case when Col1 is null then 1 else 0 end
     + case when Col2 is null then 1 else 0 end
     as Col3
from (

select 'A' as Col1, 'B' as Col2
union select 'A', NULL
union select NULL, NULL

) z

This returns

Col1 Col2 Col3
NULL NULL 2
A    NULL 1
A    B    0

Answer 3

You can use computed column:

CREATE TABLE testTable(
    col1 nchar(10) NULL,
    col2 nchar(10) NULL,
    col3  AS (case when col1 IS NULL then (1) else (0) end+case when col2 IS NULL then (1) else (0) end)
)

It is not a pretty solution, but should work.

If you are dealing with a big number of columns and many of them you expect to be NULL then you could use sparse columns (available in SQL Server 2008). It will be optimised for NULL and it can automatically generate XML representation for each row of data in the table.

Answer 4

select count(*) - count(ColumnName) as NumberOfNulls from yourTable

returns number of nulls in specific column. if you do this for every column you can get that data.

Answer 5

Oracle has a function NVL2() which makes this easy.

select col1,
       col2,
       col3,
       ...
        NVL2(col1,0,1)
       +NVL2(col2,0,1)
       +NVL2(col3,0,1) coln
from   whatever

Answer 6

As in a similar post, SQL is not very suited to work across different columns within a row, but muach better on working across rows.

I'd suggest to turn the table into 'individual' facts about a row, e.g.

select <key>, col1 as value From aTable
UNION
select <key>, col2 as value From aTable
UNION
... and so on for the other columns to be summed.

This can be turned into a view i.e.

create view aView as (select as above).

Then the correct answer is just

select key, count(*)
from aView
where value is null
Group By key