If the grouping column contains a null value, that row becomes its own group in the results. If the grouping column contains more than one null value, the null values form a single group. Here is an example that uses group by and the advance column, which contains some null values:
select advance, avg(price * 2) from titles group by advance
advance ------------------ ----------------- NULL NULL 0.00 39.98 2000.00 39.98 2275.00 21.90 4000.00 19.94 5000.00 34.62 6000.00 14.00 7000.00 43.66 8000.00 34.99 10125.00 5.98 15000.00 5.98 (11 rows affected)
If you are using the count(column_name) aggregate function, grouping by a column that contains null values returns a count of zero for the grouping row, since count(column_name) does not count null values. In most cases, you should use count(*) instead. This example groups and counts on the price column from the titles table, which contains null values, and shows count(*) for comparison:
select price, count(price), count(*) from titles group by price
price ------------- ----- ----- NULL 0 2 2.99 2 2 7.00 1 1 7.99 1 1 10.95 1 1 11.95 2 2 14.99 1 1 19.99 4 4 20.00 1 1 20.95 1 1 21.59 1 1 22.95 1 1 (12 rows affected)
