GROUP BY and aggregation

The idea

Sort your rows into piles, one pile per key value. Then squash each pile into a single row that says what the pile was like: how many, how much, the biggest, the average.

After the squash, only two kinds of column can survive: the key you grouped by, and aggregates computed over the pile. Anything else no longer has a single value to report.

WHERE decides which rows are allowed near a pile. HAVING decides which finished piles are allowed into the answer. They run at different moments, so they answer different questions.

Watch the collapse

How it works

A query is a pipeline, and the stages run in this order, not the order you write them:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

WHERE sees individual rows and knows nothing about groups, so it cannot mention an aggregate. HAVING sees finished groups, so it can, and it must, unless it is just repeating a key condition.

SELECT   region,
         COUNT(*)      AS orders,    -- counts rows, NULLs and all
         COUNT(amount) AS priced,    -- counts non-NULL amounts only
         SUM(amount)   AS revenue    -- also skips NULLs
FROM     orders
WHERE    amount >= 100               -- 1. keeps or drops single rows
GROUP BY region                      -- 2. one bucket per region
HAVING   SUM(amount) >= 100          -- 3. keeps or drops whole buckets
ORDER BY revenue DESC;

You rarely need both filters at once; they are shown together only to pin down the order. Here is the same thing spelled out as a loop, which is roughly what the engine does:

rows = [{"region": "north", "amount": 120}, ...]

# WHERE runs first, on one row at a time.
# NULL >= 100 is "unknown", never true, so NULL rows are dropped here.
kept = [r for r in rows if r["amount"] is not None and r["amount"] >= 100]

buckets = {}
for r in kept:
    b = buckets.setdefault(r["region"],
                           {"count_star": 0, "count_amount": 0, "total": 0})
    b["count_star"] += 1                # COUNT(*) counts the row itself
    if r["amount"] is not None:         # COUNT(col) and SUM skip NULLs
        b["count_amount"] += 1
        b["total"] += r["amount"]

# HAVING runs last, on one finished bucket at a time.
result = [(key, b) for key, b in buckets.items() if b["total"] >= 100]

Notice that a region with no surviving rows never appears in buckets at all. Grouping can only describe rows that exist; it never invents an empty pile.

Cost

n = input rows, g = distinct group keys, and g ≤ n.

The trade-off: hash grouping is faster but its memory grows with the number of distinct keys, so grouping by something huge (a user id, a timestamp to the second) can spill to disk. And a condition that could live in WHERE belongs there: it shrinks n before the expensive stage, while HAVING can only trim the g rows that already came out.

Watch out for

• Selecting a column that is neither a key nor an aggregate. SELECT region, amount ... GROUP BY region has no single amount to show. PostgreSQL rejects it; MySQL without ONLY_FULL_GROUP_BY quietly picks an arbitrary row's value, which looks fine until it silently isn't.
• Putting an aggregate in WHERE. WHERE SUM(amount) > 100 is an error: at that moment no group exists yet. Move it to HAVING. The reverse mistake is quieter: writing HAVING amount >= 100 where WHERE was meant gives a different answer, not just a slower one.
• COUNT(*) versus COUNT(col). COUNT(*) counts rows; COUNT(col) counts non-NULL values; COUNT(DISTINCT col) counts distinct non-NULL values. SUM and AVG skip NULLs too, so AVG(col) equals SUM(col)/COUNT(col), never divided by COUNT(*).
• Empty groups vanish. If a region's rows are all filtered away, the region produces no row at all. To keep a zero you need conditional aggregation, COUNT(*) FILTER (WHERE ...) / SUM(CASE WHEN ... THEN 1 ELSE 0 END), or a LEFT JOIN from a table of all regions.
• NULL results and NULL keys. SUM over an empty or all-NULL bucket is NULL, not 0: wrap it in COALESCE(SUM(x), 0). And GROUP BY does gather all the NULL keys into one single group, even though NULL = NULL is never true elsewhere.

Worked example

Eight orders, three regions, and two rows whose amount was never filled in. Plain grouping gives north 3 rows but only 2 priced ones and a total of 180, south 3 rows totalling 95, and east 2 rows with one price, totalling 200.

Now ask: which regions did at least 100 of business? That is a question about buckets, so it belongs in HAVING SUM(amount) >= 100: south's 95 falls short and the whole bucket goes, leaving north (3 rows, 180) and east (2 rows, 200).

Ask instead: counting only the big orders, what does each region look like? That is a question about rows, so it belongs in WHERE amount >= 100. Only two rows survive the filter, so north reports 1 row and 120, east reports 1 row and 200, and south disappears entirely: no rows, no bucket. Same threshold, same data, two honest but very different answers. Slide the threshold in the demo and watch the two result cards drift apart.

Check yourself