Skip to content
Tutorials Center
My learning

Topics

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.

SQL
-- PostgreSQL, SQL Server, Oracle, MySQL (ROLLUP only, as WITH ROLLUP)
SELECT genre, SUM(stock) AS units
FROM books
GROUP BY ROLLUP (genre);

Try it

SQL ROLLUP written as UNION ALL: per-genre totals and a grand total ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Two levels: author and genre, author only, grand total ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Telling a subtotal NULL from a real NULL ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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().
SQL
-- 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. ROLLUP reads the table once; the UNION ALL equivalent reads it once per level.
  • NULL is ambiguous. A NULL in a grouped column can be real data or a subtotal marker. Use GROUPING() where available, or an explicit flag column as above.
  • Order of rows is not guaranteed; add ORDER BY.
  • ROLLUP order matters. ROLLUP (country, city) gives subtotals per country; ROLLUP (city, country) gives subtotals per city, which is rarely wanted.
  • CUBE grows fast. Four columns give 16 sets.
  • Filtering totals. Use HAVING, as for any aggregate.