Joins, Grouping and Aggregation

AQA 4.10.3 — Joins, grouping and aggregation

Retrieval: the previous lesson queried a single table. Real questions ("which customer bought this product?") need data from several related tables at once — this lesson covers exactly how.

Key vocabulary

• JOIN — combines rows from two tables based on a matching condition, typically a foreign key matching a primary key.
• Aggregate function — a function that summarises many rows into one value: COUNT, SUM, AVG, MIN, MAX.
• GROUP BY — collapses rows sharing the same value in a chosen column into one group, so an aggregate function summarises each group separately rather than the whole table.

Understand — how a JOIN actually works

SELECT orders.id, customers.name, orders.quantity
FROM orders
JOIN customers ON orders.customer_id = customers.id;

For every row in orders, SQL finds the row in customers whose id matches that order's customer_id, and combines the two into one output row. This is the entire reason foreign keys exist — a join is how the relationship they define is actually used.

Exam-style worked example 1

Question: Predict the result (just the customer names, in order) of:

SELECT customers.name, orders.order_date
FROM orders
JOIN customers ON orders.customer_id = customers.id
ORDER BY orders.id;

Model answer: Walking through orders by id: order 1 (customer_id 1) → Amara Okafor; order 2 (customer_id 2) → Tomasz Nowak; order 3 (customer_id 3) → Priya Sharma; order 4 (customer_id 1) → Amara Okafor; order 5 (customer_id 4) → Liam O'Connor; order 6 (customer_id 5) → Sofia Rossi; order 7 (customer_id 3) → Priya Sharma. 7 rows, one per order — a customer's name repeats once per order they've placed.

See it — joining three tables

SELECT customers.name, products.name AS product, orders.quantity
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id;

Each additional JOIN adds one more related table into the same result, following its own foreign key.

Understand — GROUP BY and aggregation

SELECT customer_id, COUNT(*) AS order_count, SUM(quantity) AS total_items
FROM orders
GROUP BY customer_id
ORDER BY total_items DESC;

Without GROUP BY, COUNT(*) would count every row in the whole table as one single number. With GROUP BY customer_id, the rows are first split into one group per distinct customer, and COUNT(*)/SUM(quantity) are computed separately for each group — one summary row per customer.

Exam-style worked example 2

Question: Predict the result of:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

Model answer: Grouping the 7 orders by customer_id: customer 1 has orders 1 and 4 (count 2); customer 3 has orders 3 and 7 (count 2); customers 2, 4 and 5 each have exactly 1 order. Sorted by count descending, the two customers on count 2 (customers 1 and 3) come before the three customers on count 1 (customers 2, 4 and 5) — but the relative order within each tied group (which of 1/3 comes first; which order 2/4/5 appear in) is not determined by this query at all, since ORDER BY order_count DESC only specifies a rule for the count itself, not a tie-breaker for rows sharing the same count.

Understand — WHERE vs HAVING, precisely

WHERE filters individual rows, before any grouping happens. HAVING filters groups, after aggregation has already produced one summary row per group — HAVING can reference an aggregate function's result (like SUM(...)); WHERE cannot.

SELECT product_id, SUM(quantity) AS total_sold
FROM orders
GROUP BY product_id
HAVING SUM(quantity) > 1;

Debug it — diagnose, explain, fix, test, justify (incorrect WHERE/HAVING confusion)

A student intends "products with total quantity sold greater than 1" and writes:

SELECT product_id, SUM(quantity) AS total_sold
FROM orders
WHERE SUM(quantity) > 1
GROUP BY product_id;

This produces an error, not a wrong result.

1. Diagnose: does WHERE run before or after GROUP BY has produced any aggregated totals?
2. Explain: at the point WHERE is evaluated, does SUM(quantity) (a per-group total) actually exist yet for any individual row?
3. Fix: move the condition to the correct clause.
4. Test: confirm the corrected query runs and returns only products with a total quantity sold above 1.
5. Justify: explain, in one sentence, the rule for choosing between WHERE and HAVING.

(WHERE filters raw rows BEFORE grouping/aggregation happens, so SUM(quantity) genuinely does not exist yet at that point - there's no "per group total" to compare against for an individual, ungrouped row. The fix moves the condition into a HAVING clause instead: ... GROUP BY product_id HAVING SUM(quantity) > 1. Rule: filter individual rows with WHERE; filter aggregated group results with HAVING.)

Debug it — diagnose, explain, fix, test, justify (duplicate-row effect from a missing join condition)

A student writes a query intending one row per order, joining all three tables:

SELECT orders.id, customers.name, products.name
FROM orders, customers, products
WHERE orders.customer_id = customers.id;

This returns far more rows than there are orders — every order appears multiple times, once for every single product in the products table.

1. Diagnose: does this query's WHERE clause actually relate orders to products at all?
2. Explain: with orders correctly matched to customers, but products completely unconstrained, what happens when SQL combines every order-customer pair with every row in products?
3. Fix: add the missing join condition linking orders.product_id to products.id.
4. Test: confirm the corrected query returns exactly 7 rows (one per order), not 7 × 6.
5. Justify: explain why this fault produces a plausible-looking table (real customer names, real product names) rather than an obvious error.

(products is never actually linked to anything - with no join condition connecting it, SQL combines every order-customer row with EVERY row of products (a full unintended cross join), multiplying the row count by the number of products (6), giving 42 rows instead of 7. The fix adds AND orders.product_id = products.id to the WHERE clause (or, more clearly, uses explicit JOIN syntax for both relationships). This is dangerous specifically because every resulting row still contains genuinely real, correctly-spelled names - nothing about the DATA looks wrong, only the row COUNT reveals the fault.)

Common mistake

Assuming COUNT(*) and COUNT(column_name) always give the same result. COUNT(*) counts every row in a group regardless of content; COUNT(column_name) counts only rows where that specific column is not NULL — the two can genuinely differ when a column has missing values.

Check your understanding

Predict the result of: SELECT customer_id, SUM(quantity) AS total FROM orders GROUP BY customer_id HAVING SUM(quantity) > 1; (4 marks)

(Grouping by customer: customer 1 has orders of quantity 1 and 3 (total 4); customer 2 has quantity 2 (total 2); customer 3 has quantity 1 and 2 (total 3); customer 4 has quantity 1 (total 1); customer 5 has quantity 1 (total 1). HAVING SUM(quantity) > 1 excludes customers 4 and 5. Result: (1, 4), (2, 2), (3, 3).)

Challenge

Write a query that finds every product's total revenue (price × total quantity sold across all orders), using a join and GROUP BY, showing only products with revenue over £50, sorted highest first.

Looking ahead: the next lesson extends joins further — modelling genuine many-to-many relationships properly, using the order_items table.