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, 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)