Chapter 06 · Advanced SQL: Aggregates, Windows, GROUPING SETS, and MERGE
Aggregate Functions, FILTER, Ordered-Set / Hypothetical-Set Aggregates, and Custom Aggregates
Use PostgreSQL aggregates with correct NULL semantics, FILTER and aggregate ordering, ordered-set and hypothetical-set analytics, and a small reversible custom aggregate while understanding partial aggregation and parallel-safety constraints.
Learning outcomes
ServiceHub leadership asks for one KPI query: closed-order count, average resolution time, median resolution time, 90th-percentile-style service evidence, priority-specific totals, and a stable list of the most expensive work orders. A beginner can produce numbers quickly; a production query must also define which rows each metric consumes, what happens when the input set is empty, whether input order matters, and whether a user-defined aggregate can participate safely in parallel plans.
Explain how PostgreSQL feeds rows to ordinary aggregates and
why most aggregates ignore NULL inputs while
count(*) does not.
Use FILTER (WHERE ...) to compute multiple
conditional metrics from one grouped input without hiding
the row population.
Control order-sensitive aggregates with an
ORDER BY inside the aggregate call.
Use ordered-set aggregates such as
percentile_cont and hypothetical-set aggregates
such as rank(... ) WITHIN GROUP.
Create and remove a small custom aggregate while understanding transition state, combine/final functions, Partial Mode, and parallel-safety labeling.
Aggregate output is part of the relational result. Whether PostgreSQL later chooses HashAggregate, GroupAggregate, partial aggregation, or parallel workers is a separate planning question. Establish the exact result first; inspect a plan only when the plan itself answers a question.
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. What an aggregate consumes
An aggregate reduces zero or more input rows to one value per
group. PostgreSQL documents an important empty-input rule:
count returns zero, while most other
general-purpose aggregates return NULL when no rows
are selected. Also, most ordinary aggregates ignore NULL inputs.
These rules are easy to hide accidentally with
COALESCE, so decide whether “no input” and “a real
zero” are semantically the same before replacing NULL.
SELECT count(*) AS closed_rows, count(actual_minutes) AS measured_rows, sum(actual_minutes) AS total_minutes, round(avg(actual_minutes), 2) AS avg_minutesFROM app.ch06_work_orderWHERE status = 'closed';SELECT count(*) AS rows_found, sum(cost) AS raw_sum, COALESCE(sum(cost), 0) AS display_sumFROM app.ch06_work_orderWHERE status = 'does-not-exist';closed_rows | measured_rows | total_minutes | avg_minutes------------+---------------+---------------+------------9 | 9 | 855 | 95.00rows_found | raw_sum | display_sum-----------+---------+------------0 | NULL | 0.00
COALESCE(sum(cost),0) is reasonable when the report
contract says an empty set should display zero. It is wrong when
NULL is intentionally used to distinguish “no observations
exist” from “observed total is exactly zero.”
3. FILTER keeps conditional aggregates explicit
FILTER attaches a row predicate to one aggregate
call. It is especially useful when several metrics need
different subsets of the same grouped rows. The base
WHERE still defines the query population; each
FILTER then narrows the rows seen by its aggregate.
SELECT count(*) AS all_orders, count(*) FILTER (WHERE status = 'closed') AS closed_orders, count(*) FILTER (WHERE status = 'open') AS open_orders, sum(cost) FILTER (WHERE priority = 1) AS priority_1_cost, round(avg(actual_minutes) FILTER (WHERE status = 'closed'), 2) AS closed_avg_minutesFROM app.ch06_work_order;all_orders | closed_orders | open_orders | priority_1_cost | closed_avg_minutes-----------+---------------+-------------+-----------------+-------------------12 | 9 | 2 | 865.00 | 95.00
The same values can often be expressed with CASE,
but FILTER states the aggregate's row condition
directly. Do not assume it is automatically faster; compare
plans only for a real workload.
4. Order-sensitive aggregates need local ordering
An outer ORDER BY orders result rows; it does not
define the order in which values are fed to an aggregate.
Aggregates such as string_agg,
array_agg, and JSON aggregators can produce
meaningfully different values when their input order changes.
Put the ordering contract inside the aggregate.
SELECT string_agg(work_order_id::text, ',' ORDER BY cost DESC, work_order_id) AS by_cost_desc, array_agg(work_order_id ORDER BY closed_on, work_order_id) AS by_close_dateFROM app.ch06_work_orderWHERE status = 'closed';by_cost_desc | by_close_date--------------------------+------------------------10,5,3,7,1,12,2,9,6 | {1,2,3,6,5,7,9,10,12}Relying on a sorted subquery alone can be fragile if later outer processing can reorder rows before the aggregate consumes them. For order-sensitive aggregate values, keep the ORDER BY inside the aggregate call.
5. Ordered-set and hypothetical-set aggregates
Ordered-set aggregates use
WITHIN GROUP (ORDER BY ...). Their aggregated input
is the ordered expression rather than a normal argument list.
percentile_cont can interpolate between
observations, while percentile_disc returns an
actual input value. Hypothetical-set aggregates answer a
different question: what rank would a supplied row have if it
were inserted into the ordered group?
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY actual_minutes) AS median_minutes, percentile_disc(0.5) WITHIN GROUP (ORDER BY actual_minutes) AS discrete_median, rank(100) WITHIN GROUP (ORDER BY actual_minutes) AS hypothetical_rank_100FROM app.ch06_work_orderWHERE status = 'closed';median_minutes | discrete_median | hypothetical_rank_100---------------+-----------------+----------------------75 | 75 | 7
The hypothetical value 100 is not inserted. Six
closed orders have measured durations below 100 minutes, so a
hypothetical 100-minute value would begin at rank 7.
6. A reversible custom aggregate: sum of squares
A normal custom aggregate maintains a transition state. The transition function receives the current state and each input value. More sophisticated aggregates may also define a combine function so partial states from workers can be merged, a final function that converts internal state to the public result, serialization support for internal states, and moving-aggregate functions. Those support functions and the aggregate itself must be labeled for parallel safety truthfully.
| Component | Purpose | Why it matters |
|---|---|---|
SFUNC |
Advances transition state for each input row. | Defines the core reduction step. |
STYPE |
Type of the transition state. | May differ from the aggregate output type. |
COMBINEFUNC |
Merges partial states. | Needed for many partial/parallel aggregation strategies. |
FINALFUNC |
Transforms final state to result. | Useful when state is not the public result. |
PARALLEL |
Labels aggregate as SAFE/RESTRICTED/UNSAFE. | A false SAFE label can make parallel execution incorrect. |
CREATE FUNCTION app.ch06_add_square(state numeric, value numeric)RETURNS numericLANGUAGE SQLIMMUTABLEPARALLEL SAFESTRICTAS $$ SELECT state + value * value $$;CREATE AGGREGATE app.ch06_sum_squares(numeric) ( SFUNC = app.ch06_add_square, STYPE = numeric, INITCOND = '0', PARALLEL = SAFE);SELECT app.ch06_sum_squares(actual_minutes::numeric) AS sum_of_squaresFROM app.ch06_work_orderWHERE status = 'closed';SELECT p.oid::regprocedure AS aggregate_signature, a.aggtransfn::regprocedure AS transition_function, a.aggcombinefn::regprocedure AS combine_function, p.proparallelFROM pg_catalog.pg_aggregate aJOIN pg_catalog.pg_proc p ON p.oid = a.aggfnoidWHERE p.oid = 'app.ch06_sum_squares(numeric)'::regprocedure;DROP AGGREGATE app.ch06_sum_squares(numeric);DROP FUNCTION app.ch06_add_square(numeric, numeric);sum_of_squares--------------113225aggregate_signature | transition_function | combine_function | proparallel----------------------------------+-----------------------------------------+------------------+------------app.ch06_sum_squares(numeric) | app.ch06_add_square(numeric,numeric) | - | sThe aggregate is labeled parallel safe because its transition function is safe, but this simple teaching aggregate has no combine function. A SAFE label alone does not magically make partial aggregation possible. Production aggregate design requires a correct state algebra and truthful support-function metadata.
7. Failure analysis: an empty group is not automatically zero
-- Wrong if downstream logic assumes a numeric result.SELECT sum(cost) AS cancelled_high_priority_costFROM app.ch06_work_orderWHERE status='cancelled' AND priority=1;-- Repair only if the business contract says “no matching cost means zero”.SELECT COALESCE(sum(cost), 0)::numeric(10,2) AS cancelled_high_priority_costFROM app.ch06_work_orderWHERE status='cancelled' AND priority=1;first query -> NULLsecond query -> 0.00The repair is not a PostgreSQL tuning trick; it is an explicit semantic decision. Record that decision in the report contract so consumers know whether zero is observed data or an empty-set substitute.
8. Hands-on lab and production judgment
SELECT region, count(*) FILTER (WHERE status='closed') AS closed_orders, round(avg(actual_minutes) FILTER (WHERE status='closed'), 1) AS avg_minutes, percentile_disc(0.5) WITHIN GROUP (ORDER BY actual_minutes) FILTER (WHERE status='closed') AS median_minutes, string_agg(work_order_id::text, ',' ORDER BY cost DESC, work_order_id) FILTER (WHERE status='closed') AS closed_by_costFROM app.ch06_work_orderGROUP BY regionORDER BY region NULLS LAST;
For production, verify the input population, NULL contract,
deterministic aggregate ordering, data types, and privileges
before focusing on plan nodes. Ordered-set aggregates and large
order-sensitive aggregates can require substantial sort/work
memory; monitor actual plan evidence and temporary I/O rather
than adopting a universal work_mem value. A custom
aggregate is justified only when its state semantics,
volatility/parallel labels, upgrade ownership, tests, and
failure behavior are clearer than an equivalent built-in query.
Check your understanding
- Why can count(*) and sum(x) produce 0 and NULL respectively on the same empty input?
- What does FILTER change: the whole query population or only one aggregate input?
- Where should ORDER BY be placed when string_agg output order is part of correctness?
- How does percentile_cont differ conceptually from percentile_disc?
- Why is PARALLEL SAFE insufficient by itself to guarantee partial aggregation of a custom aggregate?
Review the answers
count(*) has a defined zero result while most aggregates return NULL on no input. FILTER narrows only the attached aggregate. Put the ordering inside an order-sensitive aggregate call. percentile_cont may interpolate while percentile_disc selects an actual ordered value. Partial aggregation also needs support such as a valid combine function/state strategy; a safety label only says the operation is allowed in parallel contexts.
9. Bridge to window functions
Aggregates collapse rows into groups. Window functions reuse many aggregate ideas while preserving individual rows, which introduces a new boundary: the window frame. Lesson 2 makes partition, peer, and frame semantics explicit before building running metrics and islands.