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