[Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL statement to get NULL count for a column

Posted on 2011-05-11
Medium Priority
Last Modified: 2012-08-13
something like

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

Must work for Oracle and Sybase
Question by:PearlJamFanatic
LVL 27

Assisted Solution

wilcoxon earned 200 total points
ID: 35743843
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
LVL 39

Accepted Solution

Pratima Pharande earned 1400 total points
ID: 35743868
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
LVL 74

Assisted Solution

sdstuber earned 200 total points
ID: 35743877
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
LVL 41

Assisted Solution

Sharath earned 200 total points
ID: 35744094
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


Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question