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.

## Be the first to comment on "NULL Values and Aggregate Functions"