Count the Null columns in a row in SQL

Question

I was wondering about the possibility to count the null columns of row in SQL, I have a table Customer that has nullable values, simply I want a query that return an int of the number of null columns for certain row(certain customer).

Answer 1

This method assigns a 1 or 0 for null columns, and adds them all together. Hopefully you don't have too many nullable columns to add up here...

SELECT    ((CASE WHEN col1 IS NULL THEN 1 ELSE 0 END)   + (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END)   + (CASE WHEN col3 IS NULL THEN 1 ELSE 0 END)   ...   ...   + (CASE WHEN col10 IS NULL THEN 1 ELSE 0 END)) AS sum_of_nulls FROM table WHERE Customer=some_cust_id 

Note, you can also do this perhaps a little more syntactically cleanly with IF() if your RDBMS supports it.

SELECT    (IF(col1 IS NULL, 1, 0)   + IF(col2 IS NULL, 1, 0)   + IF(col3 IS NULL, 1, 0)   ...   ...   + IF(col10 IS NULL, 1, 0)) AS sum_of_nulls FROM table WHERE Customer=some_cust_id 

I tested this pattern against a table and it appears to work properly.

Answer 2

My answer builds on Michael Berkowski's answer, but to avoid having to type out hundreds of column names, what I did was this:

Step 1: Get a list of all of the columns in your table

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable'; 

Step 2: Paste the list in Notepad++ (any editor that supports regular expression replacement will work). Then use this replacement pattern

• Search:

  ^(.*)$ 

• Replace:

  \(CASE WHEN \1 IS NULL THEN 1 ELSE 0 END\) + 

Step 3: Prepend SELECT identityColumnName, and change the very last + to AS NullCount FROM myTable and optionally add an ORDER BY...

SELECT      identityColumnName,      (CASE WHEN column001 IS NULL THEN 1 ELSE 0 END) +     -- ...     (CASE WHEN column200 IS NULL THEN 1 ELSE 0 END) AS NullCount FROM     myTable ORDER BY      NullCount DESC