Aggregate Across Arbitrary Dimension Combinations With GROUPING SETS

Produce multiple independent aggregation levels in one query using `GROUPING SETS` — more flexible than `ROLLUP` because you choose exactly which combinations to include.

2/2 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Get per-page totals and per-device totals without their cross-product

GROUPING SETS ((page, device), (page)) runs two separate aggregations: one grouped by both page and device, and one grouped by page alone. The results are combined into a single output. In the (page) rows, device is NULL — a grouping placeholder — and the total is the sum across all devices for that page. ORDER BY page, GROUPING(device), COALESCE(device, '~~~') places detail rows before subtotals within each page: GROUPING(device) is 0 for detail rows and 1 for subtotal rows, and the COALESCE handles the NULL device in the subtotal rows without relying on engine-specific NULLS LAST syntax. Compared to ROLLUP(page, device), this query produces detail and page-subtotal rows but not a grand total, because GROUPING SETS lets you specify exactly which combinations you need.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE web_events (
  page VARCHAR(20),
  device VARCHAR(20),
  sessions INT
);

INSERT INTO
  web_events
VALUES
  ('Home', 'Desktop', 500),
  ('Home', 'Mobile', 300),
  ('About', 'Desktop', 150),
  ('About', 'Mobile', 200);

Validated query Shared across supported engines.

SQL

SELECT
  page,
  device,
  SUM(sessions) AS total
FROM
  web_events
GROUP BY
  GROUPING SETS ((page, device), (page))
ORDER BY
  page,
  GROUPING(device),
  COALESCE(device, '~~~');

Expected result Returned rows for the shared example.

page	device	total
About	Desktop	150
About	Mobile	200
About	NULL	350
Home	Desktop	500
Home	Mobile	300
Home	NULL	800

Only PostgreSQL and SQL Server support GROUPING SETS natively. The syntax is identical between them.

Useful when

Where this command helps.

building a report that needs both per-page and per-device totals but not the per-page-per-device cross-product that CUBE would produce
replacing multiple separate aggregation queries with a single pass over the data

Explanation

What the command is doing.

GROUPING SETS ((col_a, col_b), (col_a), (col_c)) instructs the engine to run separate GROUP BY operations for each listed set and UNION the results into a single output. Unlike ROLLUP, which generates a fixed hierarchy of subtotals, GROUPING SETS lets you pick precisely the combinations you need — for example totals by region and by product category without the cross-product combinations that CUBE would produce. Grouping columns that are not part of a particular set appear as NULL in the corresponding rows. The GROUPING(col) function returns 1 for these rollup-generated NULL values and 0 for real data, making it easy to distinguish a genuine NULL in the source data from a grouping placeholder. GROUPING SETS is supported in PostgreSQL and SQL Server; MySQL, MariaDB, and SQLite do not support the syntax, but the same result can be built with UNION ALL of separate GROUP BY queries.

Aggregate Across Arbitrary Dimension Combinations With GROUPING SETS

Get per-page totals and per-device totals without their cross-product

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.