Growth Notes

Aggregation and Grouping

Tags: web-dev analytics concept

Aggregation and Grouping

Date: 2026-09-27

GROUP BY collapses rows into one per group, and every column you select must then be either a group key or an aggregate. Most SQL errors and most wrong numbers come from not knowing the order the clauses actually run in — which isn’t the order they’re written.

Aggregation reduces many rows to one value — COUNT, SUM, AVG, MIN, MAX. Grouping decides which rows get reduced together: one output row per distinct combination of the GROUP BY columns.

The collapse

events                                  SELECT channel, COUNT(*) AS events,
                                               COUNT(DISTINCT user_id) AS users
user_id  channel   event                FROM events GROUP BY channel
u1       email     page_view
u1       email     purchase             channel  events  users
u2       email     page_view            email    3       2
u3       paid      page_view            paid     2       1
u3       paid      page_view

Every output row must have one value per column. channel is fine — it’s the key. event isn’t: the email group holds page_view and purchase, and there’s no single answer. That’s the error “must appear in the GROUP BY clause or be used in an aggregate function”.

The order it really runs in

written                          executed
SELECT      5                    1  FROM / JOIN       build the rows
FROM        1                    2  WHERE             filter rows
WHERE       2                    3  GROUP BY          collapse into groups
GROUP BY    3                    4  HAVING            filter groups
HAVING      4                    5  SELECT            compute outputs, window functions
ORDER BY    6                    6  ORDER BY          sort
LIMIT       7                    7  LIMIT             cut

This one table explains most errors:

  • WHERE COUNT(*) > 5 fails — there are no groups yet at step 2. Filtering on an aggregate is HAVING
  • An alias from SELECT can’t be used in WHERE, because SELECT hasn’t run. Several dialects relax this for GROUP BY and ORDER BY — SQL Dialects
  • A window function can’t be filtered in WHERE or HAVING, because it’s computed at step 5 — Window Functions
  • Joins run first, so a fan-out join inflates every aggregate after it — Joins

WHERE vs HAVING on the same question:

-- customers with 3+ orders, counting only paid orders
SELECT customer_id, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'          -- row filter: which orders count
GROUP BY customer_id
HAVING COUNT(*) >= 3;          -- group filter: which customers survive

Moving status = 'paid' into HAVING wouldn’t work — status doesn’t exist per group. Moving the count into WHERE wouldn’t either. Put a row filter in WHERE whenever you can; it shrinks the data before the expensive step.

How the aggregates treat NULL

refund_amount:  10, NULL, 30, NULL

COUNT(*)              4       counts rows
COUNT(refund_amount)  2       counts non-NULL values
SUM(refund_amount)    40
AVG(refund_amount)    20      40 / 2 — NULLs left out of the divisor too
SUM over zero rows    NULL    not 0

AVG silently ignores nulls, so “average refund” here is the average of refunded orders (20), not across all orders (10). Which one you meant is a metric definition question — Metric Design. COALESCE(x, 0) inside the aggregate turns it into the other one.

SUM over no rows is NULL, which then turns any arithmetic it touches into NULL. Wrap the result in COALESCE(..., 0) where zero is the right answer.

COUNT(DISTINCT) doesn’t add up

users by day        users that week
Mon  2 (u1, u2)
Tue  2 (u1, u3)     3 (u1, u2, u3)    ← not 4

Distinct counts aren’t additive. Summing daily users double-counts anyone who came twice, so weekly users must be recomputed from rows, never summed from daily totals. The same applies across segments, channels and devices. It’s why pre-aggregated tables can’t answer “unique users” for an arbitrary range — Event Streams vs Aggregates.

At warehouse scale, exact distinct counts are expensive. APPROX_COUNT_DISTINCT (HyperLogLog, a probabilistic sketch) trades roughly a percent of accuracy for a large speed-up — fine for a dashboard, wrong for anything reconciled to finance. [CHECK: the error bound per engine — it varies by implementation and configuration.]

Beyond one level of grouping

  • GROUP BY ROLLUP(channel, device) adds subtotal rows per channel and a grand total, with NULL in the rolled-up column. Distinguish those from real nulls with GROUPING()
  • Conditional aggregation — several filtered counts in one pass, the pivot every funnel query needs:
SELECT channel,
  COUNT(DISTINCT CASE WHEN event = 'page_view'   THEN user_id END) AS viewed,
  COUNT(DISTINCT CASE WHEN event = 'add_to_cart' THEN user_id END) AS carted,
  COUNT(DISTINCT CASE WHEN event = 'purchase'    THEN user_id END) AS bought
FROM events
GROUP BY channel;

CASE with no ELSE yields NULL, which COUNT skips — that’s the trick. Postgres also offers COUNT(*) FILTER (WHERE ...) for the same thing — Funnel Analysis.

Traps

  • Grouping by a timestamp gives one group per instant. Truncate to the day first, in the reporting timezone rather than UTC — Timezones and Date Boundaries
  • Averages of averages. Averaging each day’s conversion rate weights a 100-visit Sunday the same as a 10,000-visit Monday. Sum the numerators and denominators, then divide — Simpson’s Paradox
  • SQLite, and MySQL with ONLY_FULL_GROUP_BY switched off, accept a bare non-grouped column and return a value from an arbitrary row in the group. No error, just a wrong answer — SQL Dialects

Graph View

Backlinks