GROUPING SETS

GROUPING SETS

GROUPING SETS for Ad Hoc Combinations

A dashboard often needs totals by unrelated dimensions at once. Without grouping sets that is three queries glued with UNION ALL, three scans. GROUPING SETS lists the groupings and computes them in one pass:

Gross sales by genre, by channel and in total, in one querySQL

SELECT genre, channel, GROUPING(genre, channel) AS grp, sum(gross_amount) AS gross
FROM mart.sales
WHERE status <> 'cancelled'
GROUP BY GROUPING SETS ((genre), (channel), ())
ORDER BY grp, gross DESC;

Output

      genre      | channel | grp |   gross
-----------------+---------+-----+------------
 Technology      |         |   1 |  934530.50
 Cooking         |         |   1 |  716952.00
 Fiction         |         |   1 |  693362.45
 Science Fiction |         |   1 |  533466.00
 Home and Garden |         |   1 |  334985.10
 Travel          |         |   1 |   90131.25
                 | ios     |   2 | 1478671.61
                 | android |   2 | 1153234.77
                 | web     |   2 |  671520.92
                 |         |   3 | 3303427.30

Columns outside the current grouping come back NULL, ambiguous when the data has NULLs. GROUPING(genre, channel) is a bitmask with a 1 bit for each rolled-up column: 2 (binary 10) marks channel rows, 3 the grand total. EXPLAIN shows a MixedAggregate that hashes all three groupings in a single scan.

MENU