SQL Reference ยท Aggregate Functions
SQL GROUPING SETS, ROLLUP and CUBE
Reference for GROUPING SETS, ROLLUP and CUBE: subtotals and grand totals in one query, the GROUPING() function, dialect support and SQLite UNION ALL.
A normal GROUP BY produces one level of grouping. GROUPING SETS lets one query produce several levels at once: for example totals per country, totals per city and a grand total. ROLLUP and CUBE are shortcuts for the most common sets.
SQLite does not support any of these. The runnable examples below build the same results with UNION ALL, and the dialect examples are read-only.
-- PostgreSQL, SQL Server, Oracle, MySQL (ROLLUP only, as WITH ROLLUP) SELECT genre, SUM(stock) AS units FROM books GROUP BY ROLLUP (genre);
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
GROUP BY GROUPING SETS ((a, b), (a), ()) GROUP BY ROLLUP (a, b) -- same as GROUPING SETS ((a, b), (a), ()) GROUP BY CUBE (a, b) -- same as GROUPING SETS ((a, b), (a), (b), ()) GROUPING(a) -- 1 if a is aggregated away in this row, else 0
| Part | Description |
|---|---|
GROUPING SETS (...) |
A list of grouping sets. Each set is aggregated separately and the results are stacked. () is the grand total. |
ROLLUP (a, b) |
Hierarchical subtotals: (a, b), (a) and (). Order matters. |
CUBE (a, b) |
Every combination of the columns: with n columns, 2^n sets. |
GROUPING(col) |
Distinguishes a subtotal row from a real NULL in the column. |
Returns: the rows of every grouping set. Columns that are not part of a set are NULL in its rows.
Dialect differences
| Database | Support |
|---|---|
| SQLite | None. Use UNION ALL or window functions. |
| PostgreSQL | GROUPING SETS, ROLLUP, CUBE, GROUPING() (9.5+). |
| MySQL | Only GROUP BY a, b WITH ROLLUP and GROUPING() (8.0+); no CUBE or GROUPING SETS. |
| SQL Server | GROUPING SETS, ROLLUP, CUBE, GROUPING() and GROUPING_ID(). |
| Oracle | GROUPING SETS, ROLLUP, CUBE, GROUPING() and GROUPING_ID(). |
-- PostgreSQL: label the subtotal rows SELECT CASE WHEN GROUPING(genre) = 1 THEN 'All genres' ELSE genre END AS genre, SUM(stock) AS units FROM books GROUP BY ROLLUP (genre); -- MySQL SELECT genre, SUM(stock) FROM books GROUP BY genre WITH ROLLUP;
Usage notes
- One scan.
ROLLUPreads the table once; theUNION ALLequivalent reads it once per level. NULLis ambiguous. ANULLin a grouped column can be real data or a subtotal marker. UseGROUPING()where available, or an explicit flag column as above.- Order of rows is not guaranteed; add
ORDER BY. ROLLUPorder matters.ROLLUP (country, city)gives subtotals per country;ROLLUP (city, country)gives subtotals per city, which is rarely wanted.CUBEgrows fast. Four columns give 16 sets.- Filtering totals. Use
HAVING, as for any aggregate.
Related
- GROUP BY, HAVING, UNION, COUNT(), pivoting data
- Lessons: ROLLUP and subtotals, GROUP BY