Advanced aggregation

Draft

Advanced aggregation is still the same idea: shape many rows into fewer rows, but with sharper tools for filters and time buckets.

HAVING

HAVING filters groups, while WHERE filters rows before grouping.

Loaded Database:advanced-aggregation.database-init

SELECT status, COUNT(*) AS payments
FROM payments
GROUP BY status
HAVING COUNT(*) > 1
ORDER BY status;

status	payments
paid	3

Sort  (cost=30.58..30.75 rows=67 width=40)
  Sort Key: status
  ->  HashAggregate  (cost=26.05..28.55 rows=67 width=40)
        Group Key: status
        Filter: (count(*) > 1)
        ->  Seq Scan on payments  (cost=0.00..20.70 rows=1070 width=32)

FILTER

FILTER lets different aggregates count different subsets in one query.

Loaded Database:advanced-aggregation.database-init

SELECT
  COUNT(*) FILTER (WHERE status = 'paid') AS paid,
  COUNT(*) FILTER (WHERE status = 'failed') AS failed
FROM payments;

paid	failed
3	1

Aggregate  (cost=31.40..31.41 rows=1 width=16)
  ->  Seq Scan on payments  (cost=0.00..20.70 rows=1070 width=32)

Group by time

date_trunc turns timestamps into buckets such as day, month, or year.

Loaded Database:advanced-aggregation.database-init

SELECT date_trunc('day', created_at) AS day, SUM(amount_cents) AS total_cents
FROM payments
WHERE status = 'paid'
GROUP BY day
ORDER BY day;

day	total_cents
2026-01-01T00:00:00.000Z	3500
2026-01-02T00:00:00.000Z	3000

GroupAggregate  (cost=23.45..23.55 rows=5 width=16)
  Group Key: (date_trunc('day'::text, created_at))
  ->  Sort  (cost=23.45..23.46 rows=5 width=12)
        Sort Key: (date_trunc('day'::text, created_at))
        ->  Seq Scan on payments  (cost=0.00..23.39 rows=5 width=12)
              Filter: (status = 'paid'::text)

What We Learned

HAVINGfilters groups after GROUP BY.
FILTERapplies a condition to one aggregate call.
date_truncrounds timestamps down to a time bucket.