SQL GROUP BY and HAVING explained

GROUP BY and HAVING

Last lesson, aggregates squashed the whole table into one row. GROUP BY squashes it into one row per group instead:

SELECT customer, COUNT(*) AS orders
FROM orders
GROUP BY customer;

Picture the database sorting the orders into piles, one pile per customer, then running COUNT(*) on each pile. You get one row per customer, each with their own count. Now customer makes sense next to COUNT(*), because every row in a pile has the same customer.

The rule: every column in SELECT should either be in the GROUP BY or be inside an aggregate. Asking for snack here would be asking "which snack represents all of Zara's orders?", and there is no good answer.

What if you only want the big spenders? You can't use WHERE SUM(total) > 10, because WHERE runs before the grouping, when the sums don't exist yet. For filtering groups there is HAVING:

SELECT customer, SUM(total) AS spent
FROM orders
GROUP BY customer
HAVING SUM(total) > 10;

Easy way to remember it:

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

You can use both in one query. The full order is now:

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

That looks like a lot, but it reads like a sentence: from this table, keep these rows, make these piles, keep these piles, sort them, take the first few.

Tables in this lesson

orders 11 rows

id	customer	snack	qty	total
1	Zara	Crisps	2	2.5
2	Leo	Energy drink	1	2.75
3	Zara	Gummy worms	3	6.0
4	Maya	Chocolate bar	4	6.0
5	Leo	Crisps	1	1.25
6	Sam	Mystery sandwich	1	3.25
7	Zara	Apple	6	3.0
8	Maya	Instant noodles	2	3.5
9	Leo	Energy drink	2	5.5
10	Leo	Crisps	3	3.75
11	Sam	Crisps	1	1.25

Show the SQL that built them

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, snack TEXT, qty INTEGER, total REAL);
INSERT INTO orders VALUES
 (1, 'Zara', 'Crisps', 2, 2.5),
 (2, 'Leo', 'Energy drink', 1, 2.75),
 (3, 'Zara', 'Gummy worms', 3, 6.0),
 (4, 'Maya', 'Chocolate bar', 4, 6.0),
 (5, 'Leo', 'Crisps', 1, 1.25),
 (6, 'Sam', 'Mystery sandwich', 1, 3.25),
 (7, 'Zara', 'Apple', 6, 3.0),
 (8, 'Maya', 'Instant noodles', 2, 3.5),
 (9, 'Leo', 'Energy drink', 2, 5.5),
 (10, 'Leo', 'Crisps', 3, 3.75),
 (11, 'Sam', 'Crisps', 1, 1.25);

Try it yourself

Edit it. Break it. Run it again.

SELECT customer, COUNT(*) AS orders, SUM(total) AS spent
FROM orders
GROUP BY customer
ORDER BY spent DESC;

Your turn

Type it yourself. That is the whole trick.

1Bestsellers

For each snack, return the snack name and the total qty sold of it.

SELECT snack, SUM(qty)
FROM orders;

2Regulars only

Return the customer and their number of orders, but only for customers with 3 or more orders.

SELECT customer, COUNT(*)
FROM orders
GROUP BY customer;