create table grptst (s_dt date, s_shop int, sales decimal(10,2), key ix1(s_shop,s_dt)); insert into grptst values ('2010-01-01',1,1000.00),('2010-01-01',2,1000.00), ('2010-01-01',1,1000.00),('2010-01-01',2,null), ('2010-01-01',null,5000.50),('2010-01-01',null,5000.50); select s_shop,s_dt,sum(sales) from grptst group by s_shop,s_dt order by 1,2; a) +--------+------------+------------+ | s_shop | s_dt | sum(sales) | +--------+------------+------------+ | 1 | 2010-01-01 | 2000.00 | | 2 | 2010-01-01 | 1000.00 | +--------+------------+------------+ 2 rows in set (0.01 sec) b) +--------+------------+------------+ | s_shop | s_dt | sum(sales) | +--------+------------+------------+ | NULL | 2010-01-01 | 10001.00 | | 1 | 2010-01-01 | 2000.00 | | 2 | 2010-01-01 | 1000.00 | +--------+------------+------------+ 3 rows in set (0.02 sec) c) +--------+------------+------------+ | s_shop | s_dt | sum(sales) | +--------+------------+------------+ | NULL | 2010-01-01 | 10001.00 | | 1 | 2010-01-01 | 2000.00 | | 2 | 2010-01-01 | NULL | +--------+------------+------------+ 3 rows in set (0.02 sec) d) +--------+------------+------------+ | s_shop | s_dt | sum(sales) | +--------+------------+------------+ | NULL | NULL | NULL | | 1 | 2010-01-01 | 2000.00 | | NULL | NULL | NULL | +--------+------------+------------+ 3 rows in set (0.02 sec)
[ Querying for Data (15%) - Aggregating Results, Grouping Results ]
Answer:
b
Summing up a group with nulls is valid and only the not-null values will be included.
SUM()
will returnNULL
if there are no rows to process. When grouping by a field which can contain nulls, thenNULL
acts like one of the field’s distinct values.