+------+-------+----------+ | ball | NULL | 5 | | ball | large | 20 | | ball | small | 10 | | ball | NULL | 35 | | hoop | NULL | 3 | | hoop | large | 5 | | hoop | small | 15 | | hoop | NULL | 23 | | NULL | NULL | 58 | +------+-------+----------+ 

To distinguish NULL  values in super-aggregate rows from those in regular grouped rows, use GROUPING() , which returns 1 only for super-aggregate NULL  values: 

mysql> SELECT name, size, SUM(quantity) AS quantity, GROUPING(name) AS grp_name, GROUPING(size) AS grp_size FROM t1 GROUP BY name, size WITH ROLLUP; +------+-------+----------+----------+----------+ | name | size | quantity | grp_name | grp_size | +------+-------+----------+----------+----------+ | ball | NULL | 5 | 0 | 0 | | ball | large | 20 | 0 | 0 | | ball | small | 10 | 0 | 0 | | ball | NULL | 35 | 0 | 1 | | hoop | NULL | 3 | 0 | 0 | | hoop | large | 5 | 0 | 0 | | hoop | small | 15 | 0 | 0 | | hoop | NULL | 23 | 0 | 1 | | NULL | NULL | 58 | 1 | 1 | +------+-------+----------+----------+----------+ 

Common uses for GROUPING() : 

•Substitute a label for super-aggregate NULL  values: 

mysql> SELECT IF(GROUPING(name) = 1, ’All items’, name) AS name, IF(GROUPING(size) = 1, ’All sizes’, size) AS size, SUM(quantity) AS quantity FROM t1 GROUP BY name, size WITH ROLLUP; +-----------+-----------+----------+ | name | size | quantity | +-----------+-----------+----------+ | ball | NULL | 5 | | ball | large | 20 | | ball | small | 10 | | ball | All sizes | 35 | | hoop | NULL | 3 | | hoop | large | 5 | | hoop | small | 15 | | hoop | All sizes | 23 | | All items | All sizes | 58 | +-----------+-----------+----------+ 

•Return only super-aggregate lines by filtering out the regular grouped lines: 

mysql> SELECT name, size, SUM(quantity) AS quantity FROM t1 GROUP BY name, size WITH ROLLUP HAVING GROUPING(name) = 1 OR GROUPING(size) = 1; 

