# Add subtotals

Shape / table

rollup, cube, grouping sets, subtotals

Page: <https://meridian.online/operations/add-subtotals>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## 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.

## 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: [`CUBE`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`GROUPING SETS`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`ROLLUP`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- Apache DataFusion: [`grouping`](https://datafusion.apache.org/user-guide/sql/aggregate_functions.html) (read 2026-09-27)
- DuckDB: [`GROUPING SETS (clause)`](https://duckdb.org/docs/current/sql/query_syntax/grouping_sets) (read 2026-09-27)
- Duckle: [`Cube`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Duckle: [`Rollup`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Power Query M: [`Cube functions (family)`](https://learn.microsoft.com/en-us/powerquery-m/accessing-data-functions) (read 2026-09-27)