• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

SQL statement to get NULL count for a column

something like

Select NULL_COUNT (col1), NULL_COUNT (col2), NULL_COUNT (col3) from table 1

Must work for Oracle and Sybase
4 Solutions
This should work on any database that follows SQL standards at all.

select count(*) from table1 where col1 is null
select count(*) from table1 where col2 is null
select count(*) from table1 where col3 is null
Pratima PharandeCommented:
Sum (Case When (col1 is null) then 1 else 0 end) as cnt_col1,
Sum (Case When (col2 is null) then 1 else 0 end) as cnt_col2,
Sum (Case When (col3 is null) then 1 else 0 end) as cnt_col3
from table 1
select count(*) allcnt,  count(col1) col1cnt, count(col2) col2cnt, count(col3) col3cnt from table

null count for col1 = allcnt - col1cnt
null count for col2 = allcnt - col2cnt
null count for col3 = allcnt - col3cnt
SharathData EngineerCommented:
For completion of sdstuber's solution.
SELECT allcnt - col1cnt AS Null_Count_Col1, 
       allcnt - col2cnt AS Null_Count_Col2, 
       allcnt - col3cnt AS Null_Count_Col3 
  FROM (SELECT COUNT(*)    allcnt, 
               COUNT(col1) col1cnt, 
               COUNT(col2) col2cnt, 
               COUNT(col3) col3cnt 
          FROM table1) AS t1  

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now