2. Joins & aggregation¶
Beginner → Intermediate · 11 min read
Real questions span tables and need totals: "revenue per city", "customers who never ordered", "average order value by plan". That's aggregation and joins.
2.1 The sample database¶
The same store data as SQL basics, plus a support tickets table:
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT, plan TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL, status TEXT, created_at TEXT);
CREATE TABLE tickets (id INTEGER PRIMARY KEY, customer_id INTEGER, category TEXT, created_at TEXT);
INSERT INTO customers VALUES
(1,'Priya','Bengaluru','pro'), (2,'Rahul','Pune','free'), (3,'Ananya','Pune','pro'),
(4,'Arjun','Delhi','free'), (5,'Meera',NULL,'free');
INSERT INTO orders VALUES
(101,1,2499,'delivered','2026-09-02'), (102,2,799,'delivered','2026-09-05'),
(103,1,1299,'shipped','2026-09-20'), (104,3,5600,'delivered','2026-09-21'),
(105,4,450,'cancelled','2026-09-22'), (106,3,999,'shipped','2026-10-01'),
(107,1,349,'delivered','2026-10-02');
INSERT INTO tickets VALUES
(1,1,'billing','2026-09-03'), (2,2,'shipping','2026-09-06'), (3,1,'shipping','2026-09-21'),
(4,3,'bug','2026-09-25'), (5,5,'billing','2026-09-30');
""")
def show(sql, params=()):
cur = con.execute(sql, params)
cols = [d[0] for d in cur.description]
rows = cur.fetchall()
widths = [max(len(str(x)) for x in [c, *[r[i] for r in rows]]) for i, c in enumerate(cols)]
print(" ".join(c.ljust(w) for c, w in zip(cols, widths)).rstrip())
for r in rows:
print(" ".join(str(x).ljust(w) for x, w in zip(r, widths)).rstrip())
2.2 Aggregate functions¶
show("""
SELECT COUNT(*) AS orders,
SUM(amount) AS revenue,
ROUND(AVG(amount), 1) AS avg_order,
MIN(amount) AS smallest,
MAX(amount) AS largest
FROM orders
WHERE status != 'cancelled'
""")
COUNT(*) counts rows; COUNT(city) counts non-NULL values of city; COUNT(DISTINCT city) counts unique
non-NULL values.
2.3 GROUP BY — one result row per group¶
show("""
SELECT status, COUNT(*) AS n, SUM(amount) AS total
FROM orders
GROUP BY status
ORDER BY total DESC
""")
Rule: every column in SELECT must be either in GROUP BY or inside an aggregate function. (SQLite is lenient
about this; PostgreSQL raises an error — and it's right to.)
2.4 HAVING — filter groups¶
WHERE filters rows before grouping; HAVING filters groups after:
show("""
SELECT customer_id, COUNT(*) AS n_orders, SUM(amount) AS spent
FROM orders
WHERE status != 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 2
""")
2.5 INNER JOIN — rows that match in both tables¶
show("""
SELECT c.name, c.city, o.id AS order_id, o.amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.amount > 1000
ORDER BY o.amount DESC
""")
name city order_id amount
Ananya Pune 104 5600.0
Priya Bengaluru 101 2499.0
Priya Bengaluru 103 1299.0
o and c are aliases — short names that keep joins readable. JOIN means INNER JOIN.
Join + group = the classic business report:
show("""
SELECT c.city, COUNT(o.id) AS orders, SUM(o.amount) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status != 'cancelled'
GROUP BY c.city
ORDER BY revenue DESC
""")
Delhi is missing — Arjun's only order was cancelled, so no rows survived the WHERE. Meera is missing — she has
no orders. If you need everyone, you need a LEFT JOIN.
2.6 LEFT JOIN — keep every row from the left table¶
show("""
SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.amount), 0) AS spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status != 'cancelled'
GROUP BY c.id
ORDER BY spent DESC
""")
Customers without orders get NULLs from the right table; COALESCE(x, 0) replaces NULL with 0.
Where you put the filter matters in a LEFT JOIN
The status != 'cancelled' condition is in the ON clause. Put it in WHERE instead and Arjun and Meera
disappear: their NULL status fails the WHERE, silently turning the LEFT JOIN back into an inner join.
| Join | Returns |
|---|---|
INNER JOIN |
only rows that match on both sides |
LEFT JOIN |
all rows from the left, matches from the right (or NULL) |
RIGHT JOIN / FULL OUTER JOIN |
all from the right / all from both (newer SQLite and PostgreSQL) |
2.7 Finding what's missing (anti-join)¶
"Customers who raised a ticket but never ordered":
show("""
SELECT c.name, t.category
FROM tickets AS t
JOIN customers AS c ON c.id = t.customer_id
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL
""")
2.8 Subqueries¶
A query inside a query:
show("""
SELECT id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders) -- above-average orders
""")
show("""
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM tickets WHERE category = 'shipping')
""")
2.9 The double-counting trap¶
Joining two "many" tables to the same parent multiplies rows:
show("""
SELECT c.name, COUNT(o.id) AS orders_wrong, COUNT(t.id) AS tickets_wrong
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
JOIN tickets AS t ON t.customer_id = c.id
WHERE c.name = 'Priya'
GROUP BY c.name
""")
Priya has 3 orders and 2 tickets, but the join pairs every order with every ticket: 3 × 2 = 6 rows. Fix: aggregate each table separately first (with subqueries or CTEs — next topic), then join the results. This is also one of the most common mistakes in LLM-generated SQL.
Practice¶
- Revenue per plan (
free/pro), including plans with no orders. - For each customer, the number of tickets and orders — without double counting.
Next: Advanced SQL — CTEs, window functions and JSON.