GROUP BY and Null Values

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-92 standard.

The royalty column in the titles table contains some null values, for example:

SELECT royalty, AVG(price * 2) AS AveragePrice
FROM pubs.dbo.titles
GROUP BY royalty

Here is the result set:

royalty     AveragePrice               
----------- -------------------------- 
(null)      (null)                     
10          32.89                      
12          30.94                      
14          23.90                      
16          45.90                      
24          5.98                       

(6 row(s) affected)

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

SELECT royalty, AVG(price * 2) AS AveragePrice
FROM pubs.dbo.titles
WHERE royalty IS NOT NULL
GROUP BY royalty