Session 4 — Aggregation: COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING

You will move from row-by-row results to summaries. The aggregate functions COUNT, SUM, AVG, MIN, MAX collapse many rows into one number; GROUP BY applies them per bucket (per genre, per author, per year); and HAVING filters the buckets after grouping — distinct from WHERE, which filters rows before. You will also learn the central rule: after grouping, every selected column is either a grouping column or an aggregate.

Plan: Core ideas → worked summaries → Quiz.

Time: about 65–75 minutes if you form the buckets by hand before writing queries.

Session 4 — Aggregation: COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING

The one-paragraph story

Sometimes you want a summary instead of a row-by-row list: total revenue, average price, books per genre. Aggregates collapse many rows into one number — COUNT counts them, SUM adds a column, AVG averages it, MIN/MAX pick extremes. On their own they summarise the whole table; combined with GROUP BY they summarise each group — one total per genre, per author, per year. HAVING then filters the groups themselves (which genres sold more than 30 copies?), which is different from WHERE filtering rows before grouping. This session builds the mental model: rows → groups → one summary row per group → filtered groups.

---

The five aggregate functions

• COUNT(*) — number of rows. COUNT(column) — number of rows where that column is not NULL.
• SUM(column) — total of a numeric column.
• AVG(column) — mean of a numeric column.
• MIN(column) / MAX(column) — smallest / largest value.

SELECT COUNT(*)        AS book_count,
       SUM(price)      AS total_value,
       AVG(price)      AS avg_price,
       MIN(price)      AS cheapest,
       MAX(published_year) AS newest
FROM books;

One row out: the whole table summarised into five numbers. Check AVG(price): the six prices sum to 143.49, divided by 6 → 23.915.

Aggregates ignore NULLs (except COUNT(*), which counts rows regardless). If price were NULL for one book, AVG(price) would divide by the number of non-null prices, not the total rows.

---

GROUP BY: one summary per group

GROUP BY breaks the rows into buckets and applies the aggregate inside each bucket:

SELECT genre, COUNT(*) AS titles
FROM books
GROUP BY genre;

| genre | titles | |-------|--------| | fiction | 2 | | mystery | 2 | | history | 2 |

Trace it: (1) FROM books — six rows. (2) GROUP BY genre — buckets fiction {10, 11}, mystery {12, 15}, history {13, 14}. (3) COUNT(*) per bucket → 2, 2, 2. (4) SELECT genre, titles — one row per bucket.

The central rule: after GROUP BY genre, each group has many rows but the query returns one row per group. Therefore the SELECT list may contain (a) the grouping column(s), and (b) aggregate expressions — but not a plain non-grouped column like title. Which single title would the fiction row show — "The Glass Harbour" or "Static Echoes"? There is no answer, so SQL forbids it. Engines differ: strict ones error, permissive ones pick arbitrarily. Never rely on the arbitrary pick.

---

WHERE vs HAVING

WHERE filters rows before grouping. HAVING filters groups after grouping.

SELECT genre, SUM(qty) AS copies
FROM books JOIN sales USING (book_id)
WHERE published_year >= 2019
GROUP BY genre
HAVING SUM(qty) > 1;

Trace the pipeline:

1. FROM + JOIN → the combined rows (each sale with its book's genre and year).
2. WHERE published_year >= 2019 → keep only recent books' sales.
3. GROUP BY genre → buckets.
4. SUM(qty) per bucket → copies per genre.
5. HAVING SUM(qty) > 1 → drop buckets with ≤ 1 copy.
6. SELECT + optional ORDER BY.

The rule of thumb: filter the rows with WHERE, filter the groups with HAVING. If the condition mentions an aggregate (SUM, COUNT, AVG…), it must be HAVING; if it mentions only row columns, it belongs in WHERE (which is also faster, since fewer rows are grouped).

---

Multiple grouping columns

GROUP BY genre, author_id buckets by the pair: each genre-and-author combination is its own bucket.

SELECT genre, author_id, COUNT(*) AS titles
FROM books
GROUP BY genre, author_id
ORDER BY genre, author_id;

Each output row is one (genre, author) pair with its count. All listed grouping columns must appear in GROUP BY; any non-aggregated SELECT column must be in the GROUP BY list.

---

Reading an aggregate query step by step

Question: "How many copies of each genre did the bookshop sell in total, most-sold first, keeping only genres with at least 4 copies?"

SELECT genre, SUM(qty) AS total_copies
FROM books JOIN sales USING (book_id)
GROUP BY genre
HAVING SUM(qty) >= 4
ORDER BY total_copies DESC;

Work the sales: fiction (books 10 sold 2+3 = 5), mystery (book 12 sold 1), history (book 14 sold 1, book 13 sold 2 → 3). Buckets → totals: fiction 5, mystery 1, history 3. HAVING SUM(qty) >= 4 keeps only fiction (5). Result: one row, fiction | 5. The WHERE/HAVING split did the work: we grouped after joining, then filtered groups.

---

Pitfalls to watch for

• WHERE filters rows, HAVING filters groups. Aggregate conditions go in HAVING.
• Non-grouped, non-aggregated columns in SELECT are the classic error. If it is not in GROUP BY and not wrapped in an aggregate, the query is broken (or engine-dependent).
• COUNT(*) counts rows; COUNT(col) counts non-NULLs. SUM(qty) over an empty bucket is NULL, not 0 — guard with COALESCE(SUM(qty), 0) when 0 is the answer you want.
• AVG ignores NULLs, so a few missing prices silently shrink the denominator.

Session 4 — Worked examples

The bookshop data again:

books: (10, The Glass Harbour, 1, fiction, 24.99, 2019), (11, Static Echoes, 1, fiction, 19.50, 2021), (12, River Clock, 2, mystery, 22.00, 2018), (13, Salt Roads, 3, history, 31.00, 2020), (14, North of Noon, 3, history, 27.50, 2017), (15, Prairie Circuit, 4, mystery, 18.00, 2022)

sales: (100, 10, 2), (101, 12, 1), (102, 10, 3), (103, 14, 1), (104, 13, 2) — (sale_id, book_id, qty)

Example A — Whole-table summary

SELECT COUNT(*)      AS book_count,
       SUM(price)    AS total_value,
       AVG(price)    AS avg_price,
       MIN(price)    AS cheapest,
       MAX(price)    AS priciest
FROM books;

| book_count | total_value | avg_price | cheapest | priciest | |------------|-------------|-----------|----------|----------| | 6 | 143.49 | 23.915 | 18.00 | 31.00 |

Verify: 24.99 + 19.50 + 22.00 + 31.00 + 27.50 + 18.00 = 143.49; 143.49 / 6 = 23.915 ✓.

---

Example B — Grouping by genre

SELECT genre, COUNT(*) AS titles, AVG(price) AS avg_price
FROM books
GROUP BY genre
ORDER BY avg_price DESC;

Buckets: fiction {10, 11}, mystery {12, 15}, history {13, 14}.

| genre | titles | avg_price | |-------|--------|-----------| | history | 2 | 29.25 | | fiction | 2 | 22.245 | | mystery | 2 | 20.00 |

Verify one: history avg = (31.00 + 27.50) / 2 = 29.25 ✓. Three buckets, three rows — each aggregate runs inside its own bucket.

---

Example C — WHERE then GROUP BY then HAVING

SELECT genre, SUM(qty) AS copies
FROM books JOIN sales USING (book_id)
WHERE published_year >= 2019
GROUP BY genre
HAVING SUM(qty) > 1
ORDER BY copies DESC;

Joined rows (book_id → genre/year, plus qty):

| sale | book | genre | published_year | qty | |------|------|-------|----------------|-----| | 100 | 10 | fiction | 2019 | 2 | | 101 | 12 | mystery | 2018 | 1 | | 102 | 10 | fiction | 2019 | 3 | | 103 | 14 | history | 2017 | 1 | | 104 | 13 | history | 2020 | 2 |

Step 1 — WHERE published_year >= 2019 drops sales 101 (2018) and 103 (2017). Remaining: 100, 102, 104. Step 2 — GROUP BY genre → fiction {100, 102}, history {104}. Step 3 — SUM(qty): fiction 2+3 = 5, history 2. Step 4 — HAVING SUM(qty) > 1 keeps both. Step 5 — sort.

| genre | copies | |-------|--------| | fiction | 5 | | history | 2 |

---

Example D — Grouping by two columns

SELECT genre, author_id, COUNT(*) AS titles
FROM books
GROUP BY genre, author_id
ORDER BY genre, author_id;

Buckets are (genre, author) pairs: (fiction, 1) → {10, 11} = 2; (mystery, 2) → {12} = 1; (mystery, 4) → {15} = 1; (history, 3) → {13, 14} = 2.

| genre | author_id | titles | |-------|-----------|--------| | fiction | 1 | 2 | | history | 3 | 2 | | mystery | 2 | 1 | | mystery | 4 | 1 |

Note mystery appears twice — a different author is a different group.

---

Example E — The forbidden SELECT column

-- Broken: which title does the 'fiction' row show?
SELECT genre, title, COUNT(*) FROM books GROUP BY genre;

title is neither a grouping column nor an aggregate — there is no sensible single value for the fiction bucket, so standard SQL rejects this. The fix is to remove title (or aggregate it, e.g. MAX(title), when that is actually what you want).

---

Example F — COALESCE for empty buckets

If a genre had zero sales, SUM(qty) for its bucket is NULL. COALESCE(SUM(qty), 0) AS copies turns that into 0 so the report reads cleanly.