CODEWITHSUNDEEP
mysqlsqlgroup-byrollup
Matt Fenwick
Matt Fenwick

Reputation: 49115

Distinguish between NULL's when using "group by ... with rollup"

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 in (what I consider to be) the PK of the query (that is, the group-by columns):

+------+------+--------+
| 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 |

... more rows follow ...

How do I distinguish (NULL, NULL, 13) from (NULL, NULL, 34)? That is, how do I distinguish between the row that has nulls because of the underlying data, and the row that has nulls because it was added by rollup? (Note that there are more examples -- (2, NULL, 6) and (2, NULL, 40))

Upvotes: 6

Views: 1628

Answers (2)

Reed Sandberg
Reed Sandberg

Reputation: 741

Answer from Cade Roux does not work for me (MySQL v5.1) and seems to be inconsistent from version to version. A method proposed on MySQL documentation comments 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:04pm

If you are grouping a column whose data contains NULLs then a NULL in the results is ambiguous as to whether it designates an actual data value or a rolled-up row.

In order to definitively know if a row is a rolled-up row 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 is the same value as the previous row's row_num is a rolled-up row and vice-versa.

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89741

Good question. One option I can think of is to do this:

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;

Upvotes: 8

Related Questions