Effect of NULL Values on Aggregate Functions
NULL values may cause aggregate functions to produce unexpected or incorrect results because these functions ignore NULL values for calculations. For example, if the Weight column of a table has 504 records, of which 299 records have NULL values, the average of this column calculated by using the AVG function may return incorrect values.
The following code example demonstrates this:
USE AdventureWorks
GO
SELECT AVG(Weight) AS 'Average Weight'
FROM Production.Product
When we execute the query, we get an average weight of 74.069219. We get this result because when calculating the average, the AVG function considers only the 205 rows that contain non-NULL values. However, it is expected that the function considers all the 504 records.
The ISNULL Function to the rescue
You can correct this problem by using the ISNULL function, as shown in the following code example:
SELECT AVG(ISNULL(Weight,0)) AS 'Average Weight'
FROM Production.Product
Now we get the right result of 30.127361.
Effect of NULL Values on the COUNT(*) Function
The COUNT(*) function is an exception to this problem because it returns the total number of records in a table, irrespective of the NULL values, as shown in the following code example:
SELECT COUNT(*) AS 'COUNT'
FROM Production.Product
When we execute this query, we get the right count of 504.
Hope this helps,
Eric Vaillancourt
http://www.sqlprof.com