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 easily 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

  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.