Get only overall summary WITH ROLLUP and GROUP BY for multiple fields

Question

Performing a WITH ROLLUP when grouping by multiple fields, MySQL returns a rollup row for each group, as well as the overall summary:

CREATE TABLE test (name VARCHAR(50), number TINYINT);
INSERT INTO test VALUES
    ('foo', 1), ('foo', 1), ('foo', 2), ('foo', 3), ('foo', 3),
    ('bar', 1), ('bar', 2), ('bar', 2), ('bar', 2), ('bar', 3),
    ('baz', 1), ('baz', 2), ('bar', 2);
SELECT name, number, COUNT(1) FROM test GROUP BY name, number WITH ROLLUP;

+------+--------+----------+
| name | number | count(1) |
+------+--------+----------+
| bar  |      1 |        1 |
| bar  |      2 |        3 |
| bar  |      3 |        1 |
| bar  |   NULL |        5 |
| baz  |      1 |        1 |
| baz  |      2 |        2 |
| baz  |   NULL |        3 |
| foo  |      1 |        2 |
| foo  |      2 |        1 |
| foo  |      3 |        2 |
| foo  |   NULL |        5 |
| NULL |   NULL |       13 |
+------+--------+----------+

I'm not inerested in the rollups for foo/bar/baz, only the overall summary. What's the most efficient way to achieve this?

Answer 1

SELECT * FROM
(
    SELECT name, number, COUNT(1) `count` FROM test
    GROUP BY name, number WITH ROLLUP
) A WHERE (ISNULL(name) + ISNULL(number)) <> 1;

or

SELECT * FROM
(
    SELECT name, number, COUNT(1) `count` FROM test
    GROUP BY name, number WITH ROLLUP
) A WHERE ISNULL(name) = ISNULL(number);

Answer 2

If all you need is the total, it's probably more efficient to use a union all query like this one:

select name, number, count(1) 
from test 
group by name, number 
union all 
select null, null, count(1) 
from test;

If you have many attributes, WITH ROLLUP will produce many subsets that you will just throw away in the outer select. I don't think the MySQL optimizer will realize that it can skip those, but it is just a guess.

Answer 3

You could also try HAVING:

SELECT name, number, COUNT(1) 
FROM test GROUP BY name, number 
WITH ROLLUP 
HAVING (number is not null or name is null);

or

HAVING (number is null) = (name is null)

Answer 4

Surely if you're only interested in the overall summary, you should just write a query that asks for it:

SELECT null, null, count(*) FROM test

(You created a test table with 13 rows, grouped on every column with rollup and then said you were only interested in the row that, effectively, counts the number of rows in the table.. so just do that)

If you're only interested in the detail rows plus the overall summary, that's what grouping sets are for:

SELECT name, number, count(*) FROM test GROUP BY GROUPING SETS ((name, number), ())

The empty set will give the summary, the name+number set will give the detail. ROLLUP is the equivalent of this grouping set:

((name, number), (name), ())

Answer 5

In this case the overall summary is equal to the amount of records (frequency).

SELECT count(1) FROM test;

If you're interested in the overall summary of the numbers then

SELECT SUM(numbers) FROM test;