In a previous post, we introduced aggregate functions and mentioned that these functions ignore NULL values. This could give us results that we did not expect or intend. However, there are several ways to handle this issue. First, for this example, we will create a table named CountTable with a single column named Numbers. Then we will INSERT six records, four records will have values and two records with NULL values.

CREATE TABLE CountTable
(Numbers tinyint)
GO

INSERT CountTable VALUES
(25), (10), (NULL), (30), (45), (NULL)

Now that we have the example table ready, let’s review the code to witness how we can use the COUNT function. The first way is when we use the COUNT function on the Number column, notice it only counts the four records that have a value. However, when we use an asterisk (*) as the parameter for the COUNT function it will count all six records. The asterisk (*) serves as a wildcard as opposed to specifying a specific column to count.

SELECT COUNT(Numbers) as CountNoNulls,
	   COUNT(*) as CountWithNulls
FROM CountTable

Working with the AVG function and NULL values is a little trickier as it does not allow an asterisk (*) as a parameter. When we use the AVG function on the Number column we get a result of 27. (Add all the rows to get 110 and then divided by 4). . This would be accurate if we wanted to know the average of only records with values. However, if we wanted to know the average whether the records have a value or not, this result would not be correct.

SELECT AVG(Numbers) as AvgNumbers,
	   SUM(Numbers)/COUNT(*) as SumDivCount,
	   AVG(ISNULL(Numbers, 0)) as AvgIsNULL
FROM CountTable

We have two options in getting the proper average that includes the records with NULL values. We could use the SUM function to total the values of the Numbers column and then divide the result by using the Count function with an asterisk (*) as the parameter. This will return the result of 18 (Add all the rows to get 110 and then divided by 6).

The second way of getting the proper average that includes the records with NULL values is to first use the ISNULL function to replace any record in the Numbers column with a 0. Then next we will place the ISNULL function inside the AVG function as the parameter value. (Note: This is called nesting functions). This again will return the result of 18 (Add all the rows to get 110 and then divided by 6).

Finally, if we wanted to evaluate NULL values across multiple columns, we could use the COALESCE function.