Aggregation collapses rows into answers

An aggregate function reduces many rows to one value: COUNT, SUM, AVG, MIN, MAX, plus statistical and ordered-set functions. Without GROUP BY, an aggregate treats the whole filtered result as a single group and returns exactly one row. With GROUP BY, it returns one row per distinct combination of the grouping expressions. That is the entire model, and almost every difficulty with aggregation comes from three places: where the filter goes, what NULL does, and whether the rows being aggregated are the rows you think they are.

Where the filter goes

Clause evaluation order decides this. WHERE runs before grouping and filters individual rows; HAVING runs after grouping and filters whole groups. So an aggregate can appear in HAVING and never in WHERE — at the time WHERE is evaluated, no groups exist yet.

The performance consequence follows directly: a predicate that does not involve an aggregate belongs in WHERE, because it eliminates rows before the grouping step rather than after it. HAVING COUNT(*) > 5 genuinely needs HAVING. HAVING region = 'EU' does not, and moving it to WHERE means fewer rows are grouped and fewer groups are built. The results are identical; the work is not.

A subtler point is that these two filters mean different things when combined. WHERE status = 'paid' ... HAVING COUNT(*) > 5 counts only paid orders and keeps groups with more than five of them. HAVING COUNT(*) FILTER (WHERE status = 'paid') > 5 counts paid orders within groups formed from all orders — so a customer with two paid and fifty unpaid orders appears in the second query's grouping but is excluded by its threshold, while in the first they never form a group large enough at all. When a report's numbers are disputed, this distinction is often the cause.

NULL in aggregation

Aggregates ignore NULL inputs. AVG(score) over ten rows where three scores are NULL divides by seven, not ten — which is usually what you want, but only if you know it. If you want the missing values treated as zero, you must say so with AVG(COALESCE(score, 0)), and the two produce genuinely different answers.

COUNT is where this is most visible. COUNT(*) counts rows, including rows that are entirely NULL. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. The difference between COUNT(*) and COUNT(col) is exactly the number of NULLs, which makes it a fast data-quality probe.

SUM over zero matching rows returns NULL, not 0 — unlike COUNT, which returns 0. This catches people constantly, because an empty result silently becomes a blank cell or a null-pointer error downstream rather than a zero. Report queries wrap it: COALESCE(SUM(amount), 0).

In GROUP BY, NULL behaves differently again: all NULLs in a grouping column are gathered into a single group, even though NULL = NULL is UNKNOWN. Grouping uses "not distinct from" rather than equality, so grouping is one of the few places where NULLs are treated as equal to each other.

Aggregating the wrong rows

The most damaging aggregation bug is not in the aggregate at all — it is join fan-out. Joining a parent to a child table with several matching rows duplicates the parent row, and a SUM of a parent column afterwards counts it once per child. The total is wrong, plausible-looking, and produces no error.

The tells are a total that grew after a join was added, or a COUNT(*) larger than you know the parent table to be. The fixes: use EXISTS when you only need to test that a related row exists; pre-aggregate the child to one row per key in a CTE or subquery and join to that; or use COUNT(DISTINCT parent.id) when you need the count of parents rather than of joined rows. Reaching for SELECT DISTINCT to make the number look right is a symptom mask, not a fix — it is also wrong whenever two legitimately different child rows share every projected column.

Multiple aggregates in one pass

Computing several differently-filtered aggregates does not require several queries or self-joins. Conditional aggregation does it in one scan:

SELECT
  customer_id,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(amount) FILTER (WHERE status = 'paid')   AS paid_revenue
FROM orders
GROUP BY customer_id;

FILTER is standard SQL and the clearest form. Where it is unavailable, the portable equivalent is SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) — note ELSE 0 for a SUM, whereas a COUNT over CASE must return NULL in the else branch, since COUNT skips NULLs but counts zeros. This is a common off-by-everything bug.

Grouping sets, rollups and windows

GROUPING SETS, ROLLUP and CUBE produce several grouping levels in one query — subtotals by region, by region and product, and a grand total — without a UNION ALL of near-identical queries. The GROUPING() function distinguishes a subtotal row's NULL from a genuine NULL in the data, which you need to render such reports correctly.

Window functions solve the different problem of keeping row detail. GROUP BY collapses; SUM(...) OVER (PARTITION BY ...) does not. If you need each order shown alongside its customer's total, or a running total, or a rank within a group, that is a window function. Because windows are computed in the SELECT phase — after WHERE, GROUP BY and HAVING — you cannot filter on a window result in WHERE; you wrap the query and filter outside. That is why every "top N per group" query has a nested shape.

Correctness and cost in reporting

Two correctness details matter disproportionately in reports. First, use exact numeric for money: summing binary floating point gives results that vary with row order, so the same query can return two different totals on two runs with different plans. Second, define date boundaries as half-open ranges — created_at >= :start AND created_at < :end — which avoids both the missing-final-day error of BETWEEN on timestamps and any dependence on time precision, and state whose time zone defines the day.

On cost: aggregation is usually resolved by a hash aggregate (building a hash table of groups) or a group aggregate (sorting, then collapsing runs). High-cardinality grouping can exceed working memory and spill to disk, which is a common cause of a query that was fast in testing and slow in production. Filtering earlier, grouping on fewer or narrower columns, and supporting indexes all help. When a dashboard repeatedly aggregates millions of rows that change slowly, the honest answer is often that it should not compute on demand at all: a materialized view or an incremental rollup table refreshed on a schedule turns an expensive scan into a cheap lookup, at the cost of bounded staleness you must state explicitly.

Worked example: join fan-out doubled the revenue

Invoice 4419 is $95 with two line items. Report joins invoices to lines then SUM(invoices.amount).

query	reported total
SUM(invoices.amount) after the join	$190
SUM in a subquery of invoices, then join lines	$95
SUM(lines.amount) grouped by invoice	$95

GROUP BY does not undo a duplicated grain. Collapse or aggregate at the true grain first.