MySQL GROUP BY WITH ROLLUP – want to ROLLUP all permutations

Programming in mysql: MySQL GROUP BY WITH ROLLUP – want to ROLLUP all permutations on newest questions tagged mysql – Stack Overflow

Take for example this table (let’s call it BIN_TABLE):

+------+------+
| A    | B    |
+------+------+
|    0 |    0 |
|    0 |    1 |
|    1 |    1 |
|    1 |    0 |
+------+------+

I want to roll it up, so I do:

SELECT   A, B, COUNT(*)
FROM     BIN_TABLE
GROUP BY A, B WITH ROLLUP;

And I get:

+------+------+----------+
| A    | B    | COUNT(*) |
+------+------+----------+
|    0 |    0 |        1 |
|    0 |    1 |        1 |
|    0 | NULL |        2 |
|    1 |    0 |        1 |
|    1 |    1 |        1 |
|    1 | NULL |        2 |
| NULL | NULL |        4 |
+------+------+----------+

This is an example of how WITH ROLLUP uses the order of the fields I put in the GROUP BY clause.

I would like to also have the following lines in the result:

| NULL |    1 |        2 |
| NULL |    0 |        2 |

Which would mean that I have all of the rolled-up permutations.

Is this possible to do without resorting to this:

SELECT   A, B, COUNT(*)
FROM     BIN_TABLE
GROUP BY A, B WITH ROLLUP
UNION
SELECT   NULL, B, COUNT(*)
FROM     BIN_TABLE
GROUP BY B

(I use MySQL 5.6, if it matters)

source: http://stackoverflow.com/questions/11286050/mysql-group-by-with-rollup-want-to-rollup-all-permutations
Programming in mysql: programming-in-mysql