Shape / table · All operations

Add subtotals

Rollup, cube, grouping sets, subtotals.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result

a new grain: each row stands for a group of input rows

Level

several clauses of SQL, or a window

The DuckDB step

Subtotals and a grand total

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

CREATE TEMP TABLE result AS
SELECT region, product, sum(amount) AS total, grouping(region, product) AS level
FROM orders GROUP BY ROLLUP (region, product) ORDER BY region NULLS LAST, product NULLS LAST;
SELECT * FROM result ORDER BY region NULLS LAST, product NULLS LAST, level;
SELECT CASE WHEN ((SELECT max(total) FILTER (WHERE level = 3) = 1900.74 AND sum(total) FILTER (WHERE level = 1) = 1900.74 AND sum(total) FILTER (WHERE level = 0) = 1900.74 FROM result)) THEN 'as expected' ELSE error('the subtotals and the grand total do not agree with the detail') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the subtotals and the grand total do not agree with the detail”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Apache DataFusion (4)

CUBEread 2026-09-27
Aggregate at every combination of the grouping expressions
GROUPING SETSread 2026-09-27
Aggregate at explicitly listed grouping levels in one query
ROLLUPread 2026-09-27
Aggregate at each prefix of the grouping list down to the grand total
groupingread 2026-09-27
Aggregate: 1 when a column is aggregated across in a grouping-set row, else 0

DuckDB (1)

GROUPING SETS (clause)read 2026-09-27
Aggregate at several groupings at once: GROUPING SETS, ROLLUP, CUBE

Duckle (2)

Cuberead 2026-09-25
aggregate with GROUP BY CUBE
Rollupread 2026-09-25
aggregate with GROUP BY ROLLUP

Power Query M (1)

Cube functions (family)read 2026-09-27
16 functions shaping a cube query: Cube.AddMeasureColumn, Cube.Dimensions, Cube.Measures, Cube.Transform, Cube.ApplyParameter

Background

The data cube operator is the N-dimensional generalisation of SQL's GROUP BY, and generalises the roll-up and sub-total constructs found in most report writers.^[1] In Oracle Database, ROLLUP creates subtotals that roll up from the most detailed level to a grand total, following its list of grouping columns.^[2] In Oracle Database, CUBE creates subtotals for all possible combinations of its grouping columns, and n columns give 2 to the n combinations.^[2]

When to use it

In PostgreSQL, ROLLUP is commonly used for analysis over hierarchical data, such as total salary by department, by division and company-wide.^[3] In Oracle Database, CUBE is typically most suitable for columns from independent dimensions, such as month, state and product, rather than the levels of a single dimension.^[2] In Oracle Database, a CUBE over year, month and day would produce subtotals of limited interest, such as the total sales for the 16th of each month across the year.^[2] Reach for ROLLUP when the columns nest inside one another, and for CUBE only when every combination is a question someone will ask. (Meridian's view)

In Oracle Database, a CUBE over 20 columns would create 2 to the 20 combinations, and a very large CUBE list could strain system resources.^[2] In Oracle Database, GROUPING SETS lets a query name just the groupings it needs, and replacing cubes with grouping sets can significantly increase performance.^[2] Ask for the groupings the report will show, and no more. (Meridian's view)

In PostgreSQL and DuckDB, a subtotal row carries NULL in the grouping columns it aggregates across, and a NULL stored in a grouping column can look the same.^[3]^[4] In Oracle Database, the GROUPING function returns 1 for a NULL created by ROLLUP or CUBE and 0 for any other value, a stored NULL included.^[2] Whenever a grouping column can be empty, label the subtotal rows from GROUPING rather than from NULL. (Meridian's view)

In Oracle Database, the conditions in a HAVING clause apply to both subtotal and non-subtotal rows, and a GROUPING or GROUPING_ID function in the HAVING clause can exclude one kind.^[2] In Oracle Database, ORDER BY makes no distinction between subtotal rows and detail rows, so sorting sales in descending order places each group's subtotal at its start.^[2] Sort on GROUPING as well as on the figures when subtotals should sit at the end of each group. (Meridian's view) Filter to a single level, using GROUPING, before adding up a result column that holds both detail rows and their subtotals. (Meridian's view)

In PostgreSQL, combining grouping items in one GROUP BY, as in GROUP BY ROLLUP (a, b), ROLLUP (a, c), can produce duplicate grouping sets, which GROUP BY DISTINCT removes.^[3]

The method

In PostgreSQL, the rows selected by the FROM and WHERE clauses are grouped separately by each grouping set, and aggregates are computed for each group as for a simple GROUP BY.^[3] In DuckDB, a query with GROUPING SETS is equivalent to a UNION ALL of one GROUP BY query per grouping set, with NULL in the columns a set leaves out.^[4] In PostgreSQL, an empty grouping set aggregates all rows down to a single group, which is output even if no input rows were present.^[3]

In PostgreSQL, ROLLUP ( e1, e2, e3, ... ) represents its list of expressions and all prefixes of the list, including the empty list.^[3] In DuckDB, ROLLUP produces n+1 grouping sets, where n is the number of terms in the ROLLUP clause.^[4] In PostgreSQL, CUBE ( e1, e2, ... ) represents its list and all of its possible subsets, the power set.^[3] In PostgreSQL, a parenthesised sublist inside CUBE or ROLLUP is treated as a single unit, so ROLLUP ( a, (b, c), d ) gives the sets (a, b, c, d), (a, b, c), (a) and ().^[3] In Oracle Database, a partial rollup such as GROUP BY expr1, ROLLUP(expr2, expr3) creates subtotals at three aggregation levels, (expr1, expr2, expr3), (expr1, expr2) and (expr1), with no grand total row.^[2] In PostgreSQL, when several grouping items appear in one GROUP BY, the final list of grouping sets is the Cartesian product of the individual items.^[3]

In PostgreSQL, GROUPING returns a bit mask in which a bit is 1 when its expression is not in the current grouping set, the rightmost argument giving the least-significant bit.^[5]

Jim Gray and seven co-authors defined the cube operator in Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals, published in Data Mining and Knowledge Discovery in 1997.^[1] The novelty of the cube operator is that cubes are relations, so the operator can be embedded in more complex non-procedural data analysis programs.^[1] The cube operator treats each of the N aggregation attributes as a dimension of N-space, and super-aggregates are computed by aggregating the N-cube to lower dimensional spaces.^[1] Many of the cube's features were being added to the SQL Standard when the Data Cube paper was written.^[1]

Compared with the alternatives

In Oracle Database, producing a cube's subtotals without CUBE takes SELECT statements joined with UNION ALL, and an n-dimensional cube needs 2 to the n of them.^[2] In Oracle Database, many SELECT statements joined with UNION ALL yield inefficient processing and very lengthy SQL.^[2] In Oracle Database, a CUBE returns every row the equivalent ROLLUP would plus further combinations, such as departmental totals across regions.^[2]

Sources

[1][cs/0701155] Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totalssecondary source
[2]SQL for Aggregation in Data Warehousesdocumentation
[3]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
[4]GROUPING SETS – DuckDBdocumentation
[5]PostgreSQL: Documentation: 18: 9.21. Aggregate Functionsdocumentation