Filter the groups
Keep only the groups that pass a test with HAVING, and know when WHERE is the right tool. About 8 minutes.
WHERE can't see the totals
You want only the cities with at least two orders. The count of orders per city doesn't exist yet when WHERE runs. WHERE looks at one row at a time, before any grouping.
HAVING runs after GROUP BY, on the finished groups. A group that fails the test is dropped as a whole.
SELECT city, COUNT(*) AS orders FROM orders GROUP BY city HAVING COUNT(*) >= 2;
| id | city | amount |
|---|---|---|
| 1 | Pune | 500 |
| 2 | Delhi | 300 |
| 3 | Pune | 900 |
| 4 | Mumbai | 120 |
| 5 | Pune | 250 |
| 6 | Mumbai | 60 |
GROUP BY city makes the groups first. Pune: 3 orders.
Use both, in order
The database works through the clauses in a fixed order: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY and LIMIT. That is why WHERE can't use a total and HAVING can.
A test about one row, like amount over 0, goes in WHERE: it's cheaper, because fewer rows get grouped. A test about a group's total or count goes in HAVING.
SELECT city, SUM(amount) FROM orders WHERE status = 'paid' GROUP BY city HAVING SUM(amount) > 1000;
Which clause can drop every city whose total amount is below 1000?
- AHAVING SUM(amount) >= 1000
- BWHERE SUM(amount) >= 1000
- CORDER BY SUM(amount)
Show the answer
HAVING SUM(amount) >= 1000. HAVING runs after grouping, so it can test a group's total.
Popular courses
The enrolments table has one row per student per course, and a grade that is NULL until it is given. Show each course that has more than 2 enrolments, with the number of enrolments as enrolled and the average grade as average. Order by course.
enrolments(id, student, course, grade)
Up to 2,000 enrolments