patternsqlMinor
Get only overall summary WITH ROLLUP and GROUP BY for multiple fields
Viewed 0 times
overallgroupwithfieldsandsummarygetformultiplerollup
Problem
Performing a
I'm not inerested in the rollups for foo/bar/baz, only the overall summary. What's the most efficient way to achieve this?
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?
Solution
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);Code Snippets
SELECT * FROM
(
SELECT name, number, COUNT(1) `count` FROM test
GROUP BY name, number WITH ROLLUP
) A WHERE (ISNULL(name) + ISNULL(number)) <> 1;SELECT * FROM
(
SELECT name, number, COUNT(1) `count` FROM test
GROUP BY name, number WITH ROLLUP
) A WHERE ISNULL(name) = ISNULL(number);Context
StackExchange Database Administrators Q#68621, answer score: 3
Revisions (0)
No revisions yet.