Skip to Content

GROUP BY & Aggregations

When to use

Collapse many rows into per-group statistics — daily per-user spend, monthly churn rate, embedding-centroid per cluster.

Analogy

GROUP BY sorts marbles by colour; aggregates (COUNT, SUM, AVG, MIN, MAX) then weigh each pile.

Data-flow diagram

  rows        GROUP BY user_id        aggregates
  +-----+      ----------------      ----------
  |  U1 |  ->   {U1: [rows]}     ->   sum, count, avg
  |  U2 |      {U2: [rows]}
  +-----+

  HAVING filters AFTER aggregation (not WHERE).
  FILTER (WHERE ...) computes several conditional aggregates
  in a single pass — cheaper than CASE WHEN.

Deep explanation

GROUP BY turns rows into per-group rows, then aggregate functions produce one value per group. WHERE filters ROWS before grouping; HAVING filters GROUPS after. The FILTER (WHERE …) clause (SQL standard, supported by Postgres) computes conditional aggregates in a single pass — far faster than wrapping each measure in CASE WHEN. For ML feature pipelines, prefer FILTER over CASE WHEN: the planner can reuse one sort/aggregate step.

Examples

Example 1

-- 5a: per-user monthly metrics with conditional aggregates
SELECT user_id,
       DATE_TRUNC('month', ts) AS month,
       COUNT(*)                     AS events,
       SUM(amount)                  AS spend,
       COUNT(*) FILTER (WHERE kind = 'click')    AS clicks,
       COUNT(*) FILTER (WHERE kind = 'purchase') AS purchases
  FROM events
 GROUP BY user_id, DATE_TRUNC('month', ts);

FILTER computes three aggregates in one scan instead of three full CASE-WHEN reaggregations.

Example 2

-- 5b: HAVING filters AGGREGATES, not rows
SELECT user_id, COUNT(*) AS n
  FROM events
 GROUP BY user_id
HAVING COUNT(*) >= 10;

HAVING runs AFTER GROUP BY — trying to filter on COUNT() with WHERE throws ‘aggregate functions are not allowed in WHERE’.

Example 3

-- 5c: percentile features (median, p95) need ordered-set aggregates
SELECT user_id,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY latency_ms) AS p50,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95
  FROM requests
 GROUP BY user_id;

percentile_cont is true continuous interpolation; for true streaming percentiles use percentile_disc or t-digest in an extension.

Common mistake

Putting aggregate filters in WHERE (WHERE COUNT(*) > 5); that throws an error. Another classic: forgetting that SELECT-target non-aggregated columns must appear in GROUP BY (Postgres enforces this; MySQL silently picks an arbitrary value, causing bugs on migration).

Key takeaway

GROUP BY fetches per-group rows; aggregates condense each group; WHERE filters rows, HAVING groups; FILTER (WHERE …) computes conditional measures cheaply; always include every non-aggregate SELECT column in GROUP BY.

Production Failure Playbook

Failure scenario 1: cardinality-blowup-on-group

Failure scenario 2: agg-vs-filter-mixup