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(<Specific Column Name Here>)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 <Specific Column Name Here> 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
*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”!
Comments (23)

  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)".

  2. Hur13y says:

    I totally NULL what you are talking about now. Thanks for teaching us Admins some SQL skills.

  3. Newbie says:

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


  4. Newbie says:

    Something with comments filter…

    SELECT COUNT( NVL( column, 0 ) )
    FROM table
    WHERE column IS NULL;

  5. KCabral says:

    Very nice job. This gave me the framework I needed to count Boolean valued columns.

  6. Vamshi says:

    thanks and one more thing here i want name’s also

  7. A says:

    Thanks. Helped me on my test. Looks like we both achieved our goals.

  8. Somebody, Somewhere, Sometime says:

    Your blog helped resolve a miscount error we had, THANK YOU FOR POSTING THIS !!

  9. MJ says:

    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.

  10. puujee says:

    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
    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)

    1. Sorry…I don’t understand your question.

  11. nagendre says:

    sry this is not correct solution

  12. MK says:

    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.



    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.

  13. IdleHost says:

    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


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

    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?

      1. I finally added some of my older (internal) tips and think the following one may be helpful for more fully answering your question:

  14. Nikhil says:

    SUM(IF(column_name is NULL, 1, 0)) as null_value_col_count

    1. That won’t work in SQL Server…can’t use “IF” in a SELECT. Rather, it is used for program flow…

  15. Radu Ci says:

    Thanks for the article, really useful.

    I would like to make a comment on “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.”. The explanation is a bit more complex for what it is meant to say, and as this might create confusion, I’d reword this to say specifically that the function counts/sums up the ones when there is NULL, as dictated by the CASE statement. The absence of an ‘ELSE’ statement is not a good argument as you could equally add an ‘ELSE 0’ statement and it would still work as desired.

    1. True we could use an “ELSE 0” when using SUM and “ELSE NULL” when using COUNT but in the end it’s essentially the same thing – we’re getting the aggregate function to ignore the NOT NULL records. I was just doing it in the most simple way rather than showing all possible ways. Perhaps I will make an edit to the post to provide more of an explanation…I’ll re-read the post and give it a thought.

  16. Yev G says:

    I used in PostgreSQL:

    SELECT COUNT(1) FROM employees WHERE email IS NULL

    It counts all NULL in EMAIL field.

    1. Yeah, I guess I should have been clearer: I only speak to SQL Server. The other forms of SQL may not behave the same as SQL Server but unless I explicitly say otherwise I’m talking only about SQL Server.

Skip to main content