SQLcountsumavgminmax

Aggregation: COUNT, SUM, AVG, GROUP BY, HAVING

GROUP BY collapses many rows into one per group; aggregates summarise each group; HAVING filters groups after they exist — and the most common aggregation bug is a join that multiplied the rows before you summed them.

Try queries Interview question See how this works internally →

Progress

Groups and what you may select

GROUP BY a, b partitions the filtered rows into one group per distinct (a, b) and the SELECT list produces one row per group. Every selected expression must therefore be either a grouping column or an aggregate over the group — there is no single value of name for a group of ten rows. PostgreSQL relaxes this when you group by a primary key: it knows the other columns of that table are functionally determined.

count(*) counts rows; count(col) counts non-NULL values of col; count(DISTINCT col) counts distinct non-NULL values and is much more expensive because it must hash or sort each group. sum, avg, min, max ignore NULLs. avg of integers is numeric; sum of an empty group is NULL.

WHERE vs HAVING

WHERE filters rows before grouping; HAVING filters groups after aggregation. WHERE count(*) > 1 is a syntax error because there is no count yet. Put every condition you *can* in WHERE — it shrinks the input to the aggregate — and reserve HAVING for conditions on the aggregate itself.

A HAVING clause is the standard tool for "find duplicates": group by the columns that should be unique and keep groups with count(*) > 1. It is also how you write "customers with more than N orders", "days with revenue above X", and every other threshold on a summary.

The fan-out bug

Join orders to order_items and each order row appears once per item. sum(orders.total) over that join counts every order as many times as it has items. The number is confidently wrong and nothing flags it. This is the most common correctness bug in reporting SQL, and it happens whenever you aggregate a column from the "one" side of a one-to-many join.

Three fixes: aggregate the many side in a subquery *before* joining; sum the column that actually lives on the many side; or use count(DISTINCT order_id) and accept the cost. What does not work is sum(DISTINCT total) — two different orders with the same total collapse into one.

Wrong, then right

1-- WRONG: each order counted once per line item
2SELECT sum(o.total)
3FROM orders o JOIN order_items oi ON oi.order_id = o.id
4WHERE o.status = 'paid';
5 
6-- RIGHT: aggregate the child first, then join
7SELECT sum(o.total)
8FROM orders o
9WHERE o.status = 'paid';                         -- the join was not needed
10 
11-- RIGHT, when you do need per-order item counts alongside:
12SELECT sum(o.total), sum(i.items)
13FROM orders o
14JOIN (SELECT order_id, count(*) AS items FROM order_items GROUP BY order_id) i
15  ON i.order_id = o.id
16WHERE o.status = 'paid';

How the engine groups

On unsorted input the executor builds a hash table keyed by the group columns — a HashAggregate — and accumulates each aggregate as rows arrive. Memory is proportional to the number of groups, not rows; a GROUP BY on a high-cardinality column can exceed work_mem and spill to disk. If the input arrives already sorted on the group key (from an index), a GroupAggregate streams through it with almost no memory. That is one reason an index on the grouping column can help a query that does not filter on it.

Aggregation over the whole table has to read the whole table — no index avoids that. Rollup tables, materialised views and incremental counters exist for aggregates that are queried far more often than the data changes.

Key points

Every selected column must be grouped or aggregated.
WHERE filters rows before grouping; HAVING filters groups after. Push conditions into WHERE.
count(*) vs count(col) vs count(DISTINCT col) mean three different things.
Aggregating above a one-to-many join multiplies the "one" side. Aggregate the child first.
HashAggregate memory scales with groups; a sorted input turns it into a streaming GroupAggregate.

Try it in the playground

SELECT date_trunc('month', created_at) AS month, count(*) AS orders, round(sum(total), 2) AS revenue
FROM orders
WHERE status IN ('paid','shipped')
GROUP BY 1
ORDER BY 1;

Revenue per month. →

SELECT order_id, count(*) AS charges
FROM payments
GROUP BY order_id
HAVING count(*) > 1;

HAVING finds the orders charged twice. →

When to use — and when not

Use it when

Any summary: totals, counts, per-group statistics.
Duplicate detection via HAVING count(*) > 1.

Avoid it when

Per-row values that need the surrounding rows kept — that is a window function, see Window Functions.

Failure modes

Fan-out from a join inflating sums.
sum() returning NULL on an empty group and breaking arithmetic downstream.
count(DISTINCT) on a huge group as a substitute for fixing the join.

See how this works internally →

Descend one layer: the same topic explained from the machinery up.

Where do GROUP BY and ORDER BY sit in the execution pipeline, and when do they spill to disk?

↓ Database Internals → Follow the Query

← SELECT, FROM, WHERE, ORDER BY, LIMIT Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF →