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.

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

Loading plan...

FILTER lets different aggregates count different subsets in one query.

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

Loading plan...

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

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;

Loading plan...