Chapter 8

Chapter 7 — Aggregate Functions and GROUP BY

Every query so far has returned one row per row in the table. Aggregate functions change that — they collapse many rows into a single summary value. Combined with GROUP BY, they let you produce per-category summaries: total sales per genre, average price per author, number of orders per customer. This chapter covers the five core aggregate functions, how GROUP BY works, and how HAVING lets you filter those summaries.

1. The Five Core Aggregate Functions

Aggregates across the whole table

Without GROUP BY, an aggregate function collapses the entire table into one row:

All six books collapsed into one summary row. avg_price is rounded here for display — MySQL returns the full decimal.

2. NULL Behaviour in Aggregates

All aggregate functions silently ignore NULL values — except COUNT(*) which counts rows regardless. This asymmetry causes a common mistake with AVG:

3. GROUP BY — Per-Category Summaries

GROUP BY splits the rows into groups — one group per distinct value of the grouping column(s) — and runs the aggregate function separately on each group. The result has one row per group.

Four genres, four result rows. MySQL grouped all Sci-fi books together, calculated COUNT/AVG/SUM for that group, then repeated for each genre.

Grouping by multiple columns

You can group by two or more columns to get a cross-tabulation. Each unique combination of the grouped columns becomes its own row:

The golden rule of GROUP BY

4. HAVING — Filtering Groups

WHERE filters individual rows before grouping. HAVING filters groups after the aggregate has been calculated. It's the only place you can reference aggregate functions in a filter condition.

Fiction (1 book) and Mystery (1 book) were excluded by HAVING COUNT(*) > 1.

WHERE vs HAVING — the key distinction

5. Full Clause Execution Order

MySQL evaluates clauses in a fixed logical order that is different from the order you write them. Understanding this explains every "column not found" and "can't use aggregate in WHERE" error:

6. Practical Patterns

Revenue per customer

Customer 1 (Alice) has placed two orders totalling £28.97 — the highest spender.

Finding customers who spent more than £15 total

Books per author — minimum one book

Author 1 (Matt Haig) has two books in the catalogue. IDs rather than names appear here — Chapter 8 (JOINs) will combine both tables to show the name alongside.

Counting NULL vs non-NULL — COUNT(*) vs COUNT(col)

One author has no nationality recorded (Ursula Le Guin's nationality was set to NULL in Chapter 5). COUNT(*) caught it; COUNT(nationality) skipped it.

WITH ROLLUP — adding a grand total row

WITH ROLLUP is a GROUP BY modifier that appends an extra summary row for each grouping level, with the final row representing the grand total across all groups:

The final row is the grand total across all genres — added automatically by WITH ROLLUP.

Chapter Summary

Chapter 8 — JOINs

Every chapter so far has queried a single table. Real databases split data across multiple related tables — orders live in orders, the customer name lives in customers, the book title lives in books. A JOIN combines rows from two or more tables based on a matching column, letting you query that connected data as if it were one result set.

1. Table Aliases — Keeping Queries Readable

JOIN queries reference two or more tables. Qualifying every column name with the full table name (orders.customer_id, customers.customer_id) gets verbose fast. A table alias — a short nickname assigned in the FROM clause — keeps things concise:

The alias follows the table name in the FROM or JOIN clause, with or without the optional keyword AS. Both forms are identical — orders o and orders AS o mean the same thing.

We'll use these single-letter aliases consistently throughout this chapter.

2. INNER JOIN — Only Matching Rows

An INNER JOIN returns rows where the ON condition matches in both tables. Rows that have no match on the other side are silently excluded. JOIN and INNER JOIN are identical — INNER is optional.

Three tables joined in one query. Customer IDs and book IDs are replaced with the real names and titles.

Joining all four tables — a full order receipt

All four tables joined in one query. Notice David (customer 4) was added in Chapter 5 but never ordered — he won't appear here because INNER JOIN only keeps matched rows.

3. JOIN Types at a Glance

4. LEFT JOIN — Keeping Unmatched Left Rows

LEFT JOIN returns all rows from the left table, whether or not they have a matching row in the right table. When there is no match, every column from the right table is NULL in that result row.

David (customer 4) has never ordered — LEFT JOIN still includes him, with NULLs for the orders columns.

LEFT JOIN + aggregate — counting orders per customer including zeros

David appears with 0 orders and £0.00 spent. COUNT(o.order_id) returns 0 (not COUNT(*)) because o.order_id is NULL for David — COUNT(col) skips NULLs. COALESCE converts NULL SUM to 0.00.

5. Anti-Join — Finding Rows With No Match

Combine LEFT JOIN with WHERE right_table.col IS NULL to find rows that have no corresponding entry in the joined table. This is one of the most practical JOIN patterns in real applications:

The Left Hand of Darkness has never been ordered — a good candidate for a promotion or a stock review.

6. RIGHT JOIN

RIGHT JOIN keeps all rows from the right table and NULLs for the left where there is no match — the mirror image of LEFT JOIN. In practice, RIGHT JOIN is rarely used because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, which most developers find easier to read:

7. Self-Join — Joining a Table to Itself

A self-join joins a table to itself using two different aliases. It's used when rows in the same table have a relationship to each other — the classic example is an employees table where each employee has a manager_id that points to another row in the same table:

The bookshop schema doesn't have a recursive relationship, but self-joins come up often in organisational charts (employees + managers), category hierarchies (parent category → child category), and graph structures stored in relational tables.

8. Combining JOINs With Everything Else

JOINs compose cleanly with every clause from previous chapters. The execution order stays the same — FROM + JOIN happens first, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT:

Sales report by genre

Best-selling authors

Ursula Le Guin doesn't appear — her book has no orders (INNER JOIN excludes her). Use LEFT JOIN chains if you need to include authors with zero sales.

ON vs WHERE — filtering in the right place

Chapter Summary