Null Values in Group By

It is known that Null <> Null. When two different NULL values are compared, the result is NULL (not TRUE), i.e. the two NULL values are not considered to be equal. Applying the same rule to the GROUP BY clause would force SQL to place each row with a NULL grouping column into a separate group by itself.

But creating a separate group for every row with a NULL in a grouping column is confusing and of no useful value, so designers wrote the SQL standard such that NULL values are considered equal for the purposes of a GROUP BY clause. Therefore, if two rows have NULL values in the same grouping columns and matching values in the remaining non-NULL grouping columns, the DBMS will group the rows together.

Simply put if the grouping column contains more than one null value, the null values are put into a single group.

For example, the grouped query:

SELECT A, B,SUM(amount_purchased) AS 'C'
FROM customers
GROUP BY A, B
ORDER BY A, B

will display a results table similar to

A	B	C
NULL	NULL	61438.0000
NULL	101	196156.0000
AZ	NULL	75815.0000
AZ	103	36958.0000
CA	101	78252.0000
LA	NULL	181632.0000

for CUSTOMERS that contain the following rows.

A	B	amount_purchased
NULL	NULL	45612.00000
NULL	NULL	15826.00000
NULL	101	45852.0000
NULL	101	74815.0000
NULL	101	75489.0000
AZ	NULL	75815.0000
AZ	103	36958.0000
CA	101	78252.0000
LA	NULL	96385.0000
LA	NULL	85247.0000