sql · level 2 · unit 1 · lesson 3
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
WHERE picks rows. picks groups.
Which genres have more than one film? WHERE cannot answer that. It looks at one row at a time, before any groups exist, and a single row has nothing to count.
HAVING works after the grouping. It looks at each group's aggregate and keeps only the groups that pass. It goes straight after GROUP BY, before ORDER BY.
You can use both in one query: WHERE sieves the rows, then HAVING sieves the groups made from them.
SELECT genre, COUNT(*) AS films
FROM films
GROUP BY genre
HAVING COUNT(*) > 1;Put COUNT(*) in WHERE and SQLite stops with misuse of aggregate function COUNT().
guess first
Players over 100 points. What comes back?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT player, SUM(points) AS total
FROM scores
GROUP BY player
HAVING SUM(points) > 100
ORDER BY total DESC, player;your turn
Your turn. Find the well-stocked kinds.
Type a number in the blank. The query lists each kind of snack whose total stock is above it. Try 10, then try 30.
result
Your answer appears here as a table.
fix it
Which kinds have more than two snacks? Fix it!
result
Your answer appears here as a table.
- Read the error. Which function does it name?
WHERElooks at one row at a time, and a single row has nothing to count yet.- Delete the WHERE line and put HAVING COUNT(*) > 2 after GROUP BY kind, then press run.
nailed it
you picked out whole groups. :D
WHEREpicks rows before grouping.HAVINGpicks groups after.HAVINGgoes straight afterGROUP BY.- Tests on aggregates belong in
HAVING, never inWHERE.
new words in your collection
- HAVING
This lesson isn’t saved. A free account remembers every lesson you finish.