Chapter 06 · Advanced SQL: Aggregates, Windows, GROUPING SETS, and MERGE
GROUPING SETS, CUBE, ROLLUP, and Multi-Level Analytical Reports
Produce multiple PostgreSQL aggregation levels in one query with GROUPING SETS, ROLLUP, and CUBE, distinguish subtotal NULLs from real NULL data using GROUPING(), and compare maintainability and plan evidence with repeated UNION ALL reports.
Learning outcomes
ServiceHub's monthly report needs totals by region and team,
totals by region, totals by team, and one grand total. Repeating
four nearly-identical queries with UNION ALL works,
but it duplicates filters and expressions and makes it easy for
one branch to drift. PostgreSQL grouping sets let one grouping
specification describe several aggregation levels.
Express several aggregation levels with
GROUPING SETS and understand its UNION-ALL-like
relational meaning.
Use ROLLUP for hierarchical prefixes and
CUBE for all subsets of selected grouping
dimensions.
Use GROUPING() to distinguish subtotal
placeholders from real NULL values in source data.
Create stable report labels without incorrectly applying
COALESCE to subtotal and data NULLs alike.
Compare grouping-set and repeated-UNION designs using readability, exact results, and plan evidence rather than universal speed claims.
1. Chapter 06 lab bootstrap
All five lessons use one small, deterministic
ServiceHub dataset. Run this in the disposable
servicehub_lab database as
servicehub_owner or another role that owns the
app schema. The objects are intentionally prefixed
ch06_ so Chapter 06 can be reset without touching
earlier chapters.
The reset is intentionally explicit and does not use CASCADE. Verify the database and matching objects before executing it. If any ch06_* object contains valuable data, stop and use another disposable lab database.
\echo 'Verify target before recreating Chapter 06 objects'SELECT current_database(), current_user;\dt app.ch06_*DROP VIEW IF EXISTS app.ch06_region_team_report;DROP AGGREGATE IF EXISTS app.ch06_sum_squares(numeric);DROP FUNCTION IF EXISTS app.ch06_add_square(numeric, numeric);DROP TABLE IF EXISTS app.ch06_inventory_stage;DROP TABLE IF EXISTS app.ch06_inventory;DROP TABLE IF EXISTS app.ch06_daily_metric;DROP TABLE IF EXISTS app.ch06_work_order;CREATE TABLE app.ch06_work_order ( work_order_id integer PRIMARY KEY, customer_id integer NOT NULL, region text, team_code text NOT NULL, status text NOT NULL CHECK (status IN ('open','closed','cancelled')), priority smallint NOT NULL CHECK (priority BETWEEN 1 AND 4), opened_on date NOT NULL, closed_on date, actual_minutes integer CHECK (actual_minutes >= 0), cost numeric(10,2) NOT NULL CHECK (cost >= 0));INSERT INTO app.ch06_work_order VALUES(1, 1,'North', 'A','closed', 1,'2026-08-01','2026-08-01', 40,200.00),(2, 2,'North', 'A','closed', 2,'2026-08-01','2026-08-02', 90,150.00),(3, 3,'North', 'B','closed', 1,'2026-08-02','2026-08-02', 60,300.00),(4, 4,'South', 'B','open', 2,'2026-08-02',NULL, NULL,120.00),(5, 4,'South', 'B','closed', 3,'2026-08-03','2026-08-04',180,500.00),(6, 5,'South', 'C','closed', 1,'2026-08-03','2026-08-03', 30, 80.00),(7, 1,'Central','A','closed', 2,'2026-08-04','2026-08-05',120,250.00),(8, 6,'Central','C','open', 1,'2026-08-04',NULL, NULL,110.00),(9, 7,NULL, 'C','closed', 2,'2026-08-05','2026-08-05', 75,130.00),(10,2,'North', 'A','closed', 3,'2026-08-05','2026-08-06',210,600.00),(11,8,'South', 'C','cancelled',4,'2026-08-06','2026-08-06',10, 0.00),(12,3,'Central', 'B','closed', 1,'2026-08-06','2026-08-07', 50,175.00);CREATE TABLE app.ch06_daily_metric ( metric_id integer PRIMARY KEY, metric_date date NOT NULL, region text, completed_count integer NOT NULL CHECK (completed_count >= 0), revenue numeric(10,2) NOT NULL CHECK (revenue >= 0), UNIQUE NULLS NOT DISTINCT (metric_date, region));INSERT INTO app.ch06_daily_metric VALUES(1,'2026-08-01','North', 1,200.00),(2,'2026-08-02','North', 2,450.00),(3,'2026-08-03','South', 1, 80.00),(4,'2026-08-04','South', 1,500.00),(5,'2026-08-05','Central', 1,250.00),(6,'2026-08-05',NULL, 1,130.00),(7,'2026-08-06','North', 1,600.00),(8,'2026-08-07','Central', 1,175.00);CREATE TABLE app.ch06_inventory ( sku text PRIMARY KEY, description text NOT NULL, on_hand integer NOT NULL CHECK (on_hand >= 0), reorder_point integer NOT NULL CHECK (reorder_point >= 0), active boolean NOT NULL DEFAULT true, updated_at timestamptz NOT NULL DEFAULT clock_timestamp());INSERT INTO app.ch06_inventory (sku, description, on_hand, reorder_point) VALUES('A-100','Fuse kit',5,5),('B-200','Patch cable',20,10),('C-300','Pump seal',7,3);CREATE TABLE app.ch06_inventory_stage ( sku text PRIMARY KEY, description text NOT NULL, on_hand integer NOT NULL CHECK (on_hand >= 0), reorder_point integer NOT NULL CHECK (reorder_point >= 0));INSERT INTO app.ch06_inventory_stage VALUES('A-100','Fuse kit',8,5),('B-200','Patch cable',0,10),('D-400','Sensor battery',12,4);
The seed contains nine closed work orders, two open work orders,
one cancelled work order, a real NULL region,
repeated dates, and a small inventory snapshot. Those details
create observable edge cases for aggregate NULL handling,
peer-aware window frames, subtotal NULLs, and DML
synchronization.
2. GROUPING SETS is one specification of multiple groups
Each grouping set is processed as if it were an independent
GROUP BY level and the results were combined. The
empty grouping set () means the grand total.
PostgreSQL can implement the work in a plan that shares scans or
aggregation machinery, but the semantic result—not a guaranteed
physical strategy—is the reason to choose the construct.
SELECT region, team_code, count(*) FILTER (WHERE status='closed') AS closed_orders, sum(cost) FILTER (WHERE status='closed') AS closed_cost, GROUPING(region) AS g_region, GROUPING(team_code) AS g_teamFROM app.ch06_work_orderGROUP BY GROUPING SETS ( (region, team_code), (region), (team_code), ())ORDER BY g_region, region NULLS LAST, g_team, team_code NULLS LAST;
A value of GROUPING(region)=1 means
region is absent from that grouping set and the
output NULL is a subtotal placeholder. A zero means region
participates in the grouping set—even if the actual data value
is NULL.
3. The real-NULL trap: COALESCE alone cannot label subtotals
The seed deliberately contains work order 9 with a real NULL
region. If you write
COALESCE(region,'ALL REGIONS'), that genuine
missing/unknown region is indistinguishable from a subtotal row
in which region was not grouped. Use
GROUPING first; only then decide how to display a
real NULL.
SELECT CASE WHEN GROUPING(region)=1 THEN 'ALL REGIONS' WHEN region IS NULL THEN '(unknown region)' ELSE region END AS region_label, CASE WHEN GROUPING(team_code)=1 THEN 'ALL TEAMS' ELSE team_code END AS team_label, count(*) FILTER (WHERE status='closed') AS closed_orders, COALESCE(sum(cost) FILTER (WHERE status='closed'),0) AS closed_cost, GROUPING(region) AS g_region, GROUPING(team_code) AS g_teamFROM app.ch06_work_orderGROUP BY GROUPING SETS ((region,team_code),(region),())ORDER BY g_region, region NULLS LAST, g_team, team_code NULLS LAST;If an “unknown region” detail row and the grand-total row both appear as ALL REGIONS, the bug is not missing data—it is a report-labeling error. GROUPING() records whether the column is present in the grouping set and is the correct discriminator.
4. ROLLUP expresses a hierarchy of prefixes
ROLLUP(region, team_code) represents
(region,team_code), (region), and
(). It is useful when the grouping dimensions form
a reporting hierarchy. It does not automatically know
organizational parent-child relationships; the hierarchy is the
order you wrote.
SELECT region, team_code, count(*) AS orders, sum(cost) AS cost, GROUPING(region) AS g_region, GROUPING(team_code) AS g_teamFROM app.ch06_work_orderWHERE status <> 'cancelled'GROUP BY ROLLUP (region, team_code)ORDER BY GROUPING(region), region NULLS LAST, GROUPING(team_code), team_code NULLS LAST;
ROLLUP(team_code, region) is a different report: it
produces team subtotals instead of regional subtotals. Ordering
of dimensions is therefore semantic.
5. CUBE produces every subset
CUBE(region, team_code) represents four grouping
sets: (region,team_code), (region),
(team_code), and (). With more
dimensions, the number of grouping sets grows as a power set. A
cube is valuable when those cross-dimensional subtotals are
actually required; it is wasteful when the consumer only needs a
simple hierarchy.
SELECT region, team_code, sum(cost) FILTER (WHERE status='closed') AS closed_cost, GROUPING(region, team_code) AS grouping_maskFROM app.ch06_work_orderGROUP BY CUBE (region, team_code)ORDER BY grouping_mask, region NULLS LAST, team_code NULLS LAST;
GROUPING(region, team_code) packs the individual
grouping flags into a bit mask. For report code that prioritizes
clarity, separate GROUPING(region) and
GROUPING(team_code) columns can be easier to
maintain.
6. Compare with repeated UNION ALL without promising a winner
-- Grouping-set formSELECT region, count(*) AS ordersFROM app.ch06_work_orderGROUP BY GROUPING SETS ((region), ());-- Repeated-query formSELECT region, count(*) AS ordersFROM app.ch06_work_orderGROUP BY regionUNION ALLSELECT NULL AS region, count(*) AS ordersFROM app.ch06_work_order;The second form repeats the base relation and any filters/derived expressions. The first centralizes them. PostgreSQL may optimize the grouping-set form with shared work, but actual plans depend on data, statistics, expressions, version, and configuration.
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT region, team_code, count(*)FROM app.ch06_work_orderGROUP BY GROUPING SETS ((region,team_code),(region),(team_code),());Do not copy a sample plan node as a contract. Record scan counts, actual rows, aggregate strategy, sort/spill evidence, buffer usage, and elapsed summary from your own lab.
7. Hands-on lab: a production-shaped subtotal report
SELECT CASE WHEN GROUPING(region)=1 AND GROUPING(team_code)=1 THEN 'grand_total' WHEN GROUPING(team_code)=1 THEN 'region_total' ELSE 'region_team' END AS level, CASE WHEN GROUPING(region)=1 THEN NULL ELSE region END AS region, CASE WHEN GROUPING(team_code)=1 THEN NULL ELSE team_code END AS team_code, count(*) FILTER (WHERE status='closed') AS closed_orders, sum(cost) FILTER (WHERE status='closed') AS closed_costFROM app.ch06_work_orderGROUP BY ROLLUP(region, team_code)ORDER BY GROUPING(region), region NULLS LAST, GROUPING(team_code), team_code NULLS LAST;
Machine-facing reports often benefit from explicit
level plus raw nullable dimensions instead of
embedding presentation labels into the same columns. That makes
subtotal semantics easier for APIs and BI consumers to validate.
Check your understanding
- What does the empty grouping set () represent?
- Why can COALESCE(region,'ALL') corrupt a report that contains real NULL regions?
- How does ROLLUP(a,b) differ from CUBE(a,b)?
- What does GROUPING(column)=1 mean?
- Why should a grouping-set query not be described as always faster than UNION ALL?
Review the answers
The empty set is the grand total. COALESCE cannot distinguish a real NULL data value from a subtotal placeholder. ROLLUP(a,b) emits (a,b),(a),(), while CUBE(a,b) also includes (b). GROUPING=1 means the expression is absent from that grouping set. Planner strategy and workload determine performance, so measure actual plans rather than universalizing one result.
8. Bridge to observable data modification
Multi-level reads combine several relational outcomes in one statement. Lesson 4 applies the same discipline to writes: every proposed row must have a defined conflict/match classification, action, observable old/new state, and concurrency contract.