SQL statement to get NULL count for a column

Posted on 2011-05-11
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 26

    Assisted Solution

    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

    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 73

    Assisted Solution

    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 40

    Assisted Solution

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This article describes some very basic things about SQL Server filegroups.
    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now