Which aggregate function counts the number of rows including rows with null value?

SQL Tip December 27, 2013March 9, 2019 3 Minutes

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

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”!

Published December 27, 2013March 9, 2019