Distinguish NULL when using "group by ... with rollup"

Question

When I run a query using group by ... with rollup :

 select a, b, sum(c) from <table> group by a, b with rollup; 

I get duplicate rows (what I think) of a PK query (i.e. columns by group):

 +------+------+--------+ | a | b | sum(c) | +------+------+--------+ | NULL | NULL | 13 | | NULL | 1 | 4 | | NULL | 3 | 8 | | NULL | 4 | 9 | | NULL | NULL | 34 | | 1 | 3 | 17 | | 1 | 4 | NULL | | 1 | 17 | 2 | | 1 | NULL | 19 | | 2 | NULL | 6 | | 2 | 1 | 17 | | 2 | 3 | 17 | | 2 | NULL | 40 | | 4 | 17 | 2 | | 4 | NULL | 2 | | 5 | NULL | 11 | | 5 | 6 | 7 | | 5 | NULL | 18 | | 13 | 4 | 2 | | 13 | NULL | 2 | | 14 | 41 | 3 | | 14 | NULL | 3 | | 18 | 1 | 2 | | 18 | NULL | 2 | | 41 | 2 | 17 | | 41 | NULL | 17 | 

... then the lines follow ...

How to distinguish <T23> from (NULL, NULL, 34) That is, how do I distinguish between a row that has nulls due to the original data and a row that has nulls because rollup was added? (Note that there are more examples - (2, NULL, 6) and (2, NULL, 40) )

Answer 1

Good question. One of the options I can think of is to do:

 select COALESCE(a, -1) AS a, COALESCE(b, -1) AS b, sum(c) from <table> group by COALESCE(a, -1), COALESCE(b, -1) with rollup; 

Answer 2

The answer from Cade Roux does not work for me (MySQL v5.1) and seems to be incompatible with version and version. The method suggested for commenting on MySQL documentation is the only reliable method I've seen:

http://dev.mysql.com/doc/refman/5.6/en/group-by-modifiers.html

Posted by Peter Kioko on June 27, 2012 2:04 PM

If you are grouping a column whose data contains NULL, then NULL in the results is ambiguous as to whether it determines the actual value of the data or the collapsed row.

To finally find out if a string is a collapsed string or not, you can use this trick:

SET @i = 0;

SELECT @i: = @i + 1 AS row_num, year, country, product, SUM (profit) FROM sales GROUP BY year, country, product WITH ROLLUP;

In the result set, any row whose row_num matches the previous row row_num is a collapsed string and vice versa.