Group BY clause and null value
If the group column contains an null value, the line will become a group in the result. If the group column contains multiple null values, these null values will be placed in a 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
The following 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)
The following SELECT statement can be changed to delete an empty value by adding a WHERE clause:
SELECT ROYALTY, AVG (Price * 2) as averageprice
From pubs.dbo.titles
WHERE ROYALTY IS NOT NULL
GROUP BY ROYALTY
