GROUP BY and Null Values

If the grouping column contains a null value, that row becomes a group in the results. If the grouping column contains more than one null value, the null values are put into a single group. This behavior is defined in the SQL-2003 standard.

The Color column in the Product table contains some null values. For example:

SELECT Color, AVG (ListPrice) AS 'average list price'
FROM Production.Product
GROUP BY Color
ORDER BY Color

Here is the result set.

Color           average list price
NULL            16.8641
Black           998.9097
Blue            1128.0385
Grey            125.00
Multi           59.865
Red             1303.2674
Silver          1124.8334
Silver/Black    64.0185
White           9.245
Yellow          1197.6138

(10 row(s) affected)

This SELECT statement can be changed to remove the null values by adding a WHERE clause:

SELECT Color, AVG (ListPrice) AS 'average list price'
FROM Production.Product
WHERE Color IS NOT NULL
GROUP BY Color
ORDER BY Color

See Also

Concepts

Grouping Rows with GROUP BY

Help and Information

Getting SQL Server 2005 Assistance