Count the Null columns in a row in SQL

by

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.

More Related Contents:

  1. Which one of these Queries are wrong and which is correct (SQL) [closed]
  2. ORA-01843 Not a valid month
  3. Explicit vs implicit SQL joins
  4. Row Offset in SQL Server
  5. SQL Server recursive self join
  6. Insert data in 3 tables at a time using Postgres
  7. Left Outer Join Not Working?
  8. How to simulate UNPIVOT in Access?
  9. Real life example, when to use OUTER / CROSS APPLY in SQL
  10. How to get script of SQL Server data? [duplicate]
  11. Better techniques for trimming leading zeros in SQL Server?
  12. Fulltext Search with InnoDB
  13. Sql Server equivalent of a COUNTIF aggregate function
  14. Using ISNULL vs using COALESCE for checking a specific condition?
  15. PostgreSQL: Which Datatype should be used for Currency?
  16. Add unique constraint to combination of two columns
  17. What does a transaction around a single statement do?
  18. format interval with to_char
  19. Joining multiple tables in SQL
  20. How to calculate Session and Session duration in Firebase Analytics raw data?
  21. Array in IN() clause oracle PLSQL
  22. Achieving ROW_NUMBER / PARTITION BY in MS Access
  23. Get the character between first 2 special character in SQL
  24. How to get a list column names and datatypes of a table in PostgreSQL?
  25. Inner Joining three tables
  26. Postgresql query between date ranges
  27. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  28. Determine if one coordinate is in radius of another
  29. Insert all values of a table into another table in SQL
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment