SQL Tip: COUNTing NULL values

I’ve been asked about counting NULL values several times so I’m going to blog about it in hopes others will be helped by this explanation of NULL values in SQL and how to COUNT them when necessary. Note, my examples make use of a table found in the System Center Configuration Manager database.

First, it’s important to know what a NULL is in SQL. A NULL in SQL simply means no value exists for the field. Comparisons for NULL cannot be done with an “=” or “!=” (or “”) operators*. Additionally, NULL ‘values’ will not be JOINed when in a JOIN (meaning a NULL value in one table.column does not “=” a NULL value in the other table.column). This means rather than saying something like “WHERE NullableField = NULL” you must instead say “WHERE NullableField IS NULL” when trying to find NULLs (or NOT find NULLs)**.
We’re ready now to look at the solutions:
The COUNT function can tell you the total number of rows returned in a result set (both NULL and non-NULL together depending on how it’s used). For example:
·         Using SELECT COUNT(*) or SELECT COUNT(1)  (which is what I prefer to use) will return the total of all records returned in the result set regardless of NULL values.
·         Using COUNT()will count the number of non-NULL items in the specified column (NULL fields will be ignored).
Thus, you could find the number of NULL fields in the result set by subtracting the non-NULL fields from the Total fields, for example:
SELECT COUNT(1) - COUNT(<Specific Column Name Here>)
 
But, that would be boring. So, instead we’ll do it a more non-boring way. We’ll be using the CASE statement to determine which rows have NULLs and create a way for SQL to COUNT (or SUM) them. Here’s what that looks like (I could’ve used a COUNT instead of a SUM):
SUM(CASE WHEN IS NULL THEN 1 END)
 
The reason this will work is because when there is no “ELSE” in a CASE statement any row not meeting the criteria is treated as a NULL. Since the COUNT (and other aggregate functions) will ignore NULL values we use the CASE to turn NULLs into values and values into NULLs.
 
Here’s a real-life example of what this looks like (using a modified version of the query sent to me):
SELECT  LastStatusMessageIDName
       ,COUNT(1) AS [Count of Total Records]
       ,COUNT(LastExecutionResult) AS [Count of Non-NULL Records]
       ,SUM(CASE WHEN LastExecutionResult IS NULL THEN 1 END) AS [Count of NULL Records]
  FROM dbo.v_ClientAdvertisementStatus
 WHERE AdvertisementID = 'CAZ201AE'
   AND LastStateName != 'Succeeded'
 GROUP BY LastStatusMessageIDName
 ORDER BY 4 DESC;
*Technically it is possible if you tell SQL to not think like SQL, but I don’t count that as a solution or even an option. If you are using “SET ANSI_NULLS OFF” in your scripts I suggest you re-write them. Here’s what Books Online says about this option: In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
 
**To see this in a real example, try running the following to see the behavior:
/*****  Comparing a NULL field  *****/
SELECT COUNT(1) FROM dbo.v_ClientAdvertisementStatus WHERE LastExecutionResult IS NULL AND AdvertisementID = 'CAZ201AE' -- This will count the total records that have a NULL “LastExecutionResult” (for the advertisement)
SELECT COUNT(1) FROM dbo.v_ClientAdvertisementStatus WHERE LastExecutionResult = NULL AND AdvertisementID = 'CAZ201AE' -- This doesn’t work because no values technically “equal” “NULL”!

19 thoughts on “SQL Tip: COUNTing NULL values

  1. @Newbie, the "NVL" function is essentially the "ISNULL" function in SQL Server (if I’m not mistaken). That function replaces a NULL value with whatever value you provide. I don’t see why you’d use it from your examples though since doing "COUNT( NVL( column,
    0) )" would be the same thing as doing "COUNT( 0 )" or as I used in my examples "COUNT(1)".

    Like

  2. It is much better to use Oracle SQL Singel-row General function NVL, who convert null to actual value.

    SELECT COUNT(NVL( , 0))
    FROM

    WHERE IS NULL;

    Like

  3. Very Nice! even though this is basic stuff but makes a lot of sense, we miss out these things in our daily query life! Thx a lot for ur time to blog this.

    Like

  4. hi how to result 0 (SELECT direction_name,car_number,car_type_name, sitcount,isnull(COUNT(*),0) as seat_no1,leave_date

    FROM t_dispatcher_seats_view,t_dispatcher_View where
    dispatcher_id=id
    and start_sequence IS NULL AND zahialagch_id IS NULL
    and direction_id= 72 and direction_start_stop_id=1 and
    DATEDIFF(day ,t_dispatcher_View.leave_date ,GETDATE()) = 4
    group by direction_name,sitcount,car_number,car_type_name,leave_date)

    Like

  5. Nice one. !
    Is there a way in SQL Server to find out what all columns in a huge table are Totally Null ?
    Like if I have a 200 column table and I know many columns are nullable and have no data or partial data, but I would like to know what columns are completely null, not partially.

    Thanks

    MK

    Like

    1. If I understand your question correctly…the only way to do that is to check each of the columns individually. Something like:
      IF NOT EXISTS(SELECT * FROM dbo.YourTable WHERE NullableColumn1 IS NOT NULL)
      PRINT ‘NullableColumn1’;
      But you’d have to perform this on every column.

      Like

  6. This is great, thanks for the info. Is it possible to return the same results but for every column?

    i.e I have a table as follows with columns A,B,C,D and values 1 or NULL:

    A B C D
    1 1 NULL 1
    NULL 1 1 NULL
    NULL 1 1 1

    Results

    Column CTR CNNR CNR
    A 3 1 2
    B 3 3 0
    C 3 2 1
    D 3 2 1

    Like

    1. This too is possible…but would need to be done either dynamically or with a UNION ALL because you want it pivoted (showing the column name). You have to do the counts against each column. Does that make sense?

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s