MySQL only get overall ROLLUP

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 interested in the rollups for foo/bar/baz, only the overall summary. What's the most efficient way to achieve this?

I know I can't use HAVING due to the rollup rows being added afterwards. Is the best solution to use a nested query for this, selecting where name and number are either both NOT NULL or both NULL?

Solution 1

HAVING can do the trick with no subquery:

SELECT `name`, number, COUNT(1) FROM test GROUP BY `name`, number WITH ROLLUP 
HAVING number IS NOT NULL OR `name` IS NULL;

This filters out the post-rollup rows except for the grand total:

name    number  COUNT(1)
------  ------  --------
bar          1         1
bar          2         4
bar          3         1
baz          1         1
baz          2         1
foo          1         2
foo          2         1
foo          3         2
(NULL)  (NULL)        13

Solution 2

Try to use a subquery, e.g. -

SELECT * FROM (
  SELECT name, number, COUNT(1) FROM test GROUP BY name, number WITH ROLLUP) t
WHERE name IS NULL OR number IS NULL

You also may want to change NULL values with appropriate texts.

Comments

[DRapp]

If he only wants the grand total, you may want to update the where to... WHERE NOT name is null OR ( name is null AND number is null ) so it excludes only the rollups of the name/number grouping, but leaves the overall grand total count included.

[Devart]

Yes, you are right. Also, to get only the overall summary it is enough to run this query - SELECT 'TOTAL' AS total, COUNT(1) FROM test;

[cmbuckley]

I think this is quite close to what I'm after; actually I was after WHERE name IS NULL OR number IS NOT NULL. Hopefully the data sets should be small enough to remain efficient even with the subquery.

[cmbuckley]

For reference, at the time I believe I was using an outdated version of MySQL, which led to my last paragraph. 5.5 was out in 2012 which supported ROLLUP values in HAVING clause, but the older 5.0 and 5.1 did not: forums.mysql.com/read.php?12,106959,263513

[cmbuckley]

The intention was to exclude rows, not coalesce nulls.

[Lou]

I've tried this with a similar query and it filters out all the totals including the grand total - SELECT fruit, size, avg(price) FROM fruits GROUP BY fruit, size WITH ROLLUP HAVING fruit IS NULL or size IS NOT NULL;