Export (0) Print
Expand All

GROUP BY and Null Values

SQL Server 2005

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

Community Additions

ADD
Show:
© 2014 Microsoft