📊Aggregate functions summary
Stages, not vibes
This page ties together aggregates, grouping, and filtering so you can read complex reports without losing the logical order of operations. Think in stages: FROM/JOIN builds a bag, WHERE filters rows, GROUP BY forms groups, HAVING filters groups, SELECT projects, ORDER BY sorts. Aggregates attach to groups, not to arbitrary row subsets after the fact.
Optimizers may reorder joins and push predicates, but they must preserve aggregate semantics. Correlated subqueries with aggregates can become nested-loop nightmares unless rewritten as joins or lateral joins.
📐Common aggregates
Names and NULL rules differ slightly by engine, verify once.
| Function | Input | Output |
|---|---|---|
| COUNT(*) | Rows | Integer count |
| SUM(x) | Numeric | Numeric total (NULL if none) |
| AVG(x) | Numeric | Mean of non-NULL x |
| MIN/MAX(x) | Orderable | Extrema |
| STRING_AGG | Text per group | Delimited list |
FILTER clause (PostgreSQL)
SELECT department_id,
COUNT(*) FILTER (WHERE end_date IS NULL) AS active,
COUNT(*) AS total
FROM employees
GROUP BY department_id;| department_id | active | total |
|---|---|---|
| 10 | 42 | 50 |
| 20 | 31 | 33 |
Aggregate mistakes
- Using SELECT * with GROUP BY on engines that do not infer functional dependencies, syntax errors or silent bugs.
- Forgetting that window functions happen after GROUP BY, so you cannot nest them casually without a subquery.
- Letting ORMs generate duplicate joins that multiply rows before aggregation.
Professional discipline
- Write CTEs named after stages (filtered_orders, daily_totals) so reviews map to the mental model.
- Always sanity-check row counts before and after joins when building new aggregates.
- Document timezone and currency assumptions in the same file as the query.
- Add golden tests for aggregate outputs on fixed fixtures.
KPI governance
BI and finance reviews fail when two teams use subtly different SQL for the same KPI, treat canonical aggregate queries like versioned APIs.
TL;DR
Standard SQL evaluates aggregates in this mental order: FROM/JOIN → WHERE (row filter) → GROUP BY (form groups) → HAVING (group filter) → SELECT (project) → ORDER BY. Aggregates attach to groups; HAVING is where post-group filters (e.g. HAVING COUNT(*) > 1) belong, not WHERE.