Null Values and group by

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, all null values form a single group.

This example 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 include null values. In most cases, 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)