sql · level 2 · unit 3 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
After a LEFT JOIN, count a column, not *.
How many orders has each customer made, Sam included? A LEFT JOIN keeps Sam, so grouping by name gives Sam a group. But that group still holds one row: Sam paired with NULLs.
COUNT(*) counts rows, so Sam would get 1. That is an , because Sam has made no orders. COUNT(o.id) skips NULL boxes, so Sam gets 0.
SUM over nothing but NULLs gives NULL, not 0. You will learn to swap that for 0 soon.
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY orders DESC, c.name;Count a column from the right-hand table, ideally its id, which is only empty when there was no match.
guess first
Pets per owner. What does Dev get?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT o.name, COUNT(*) AS pets
FROM owners o
LEFT JOIN pets p ON p.owner_id = o.id
GROUP BY o.name
ORDER BY o.name;your turn
Your turn. Count each dish's orders.
Fill the blank so the query counts orders, not rows. Every dish should show up, with 0 for the salad nobody chose.
result
Your answer appears here as a table.
fix it
Sam has never ordered, but shows 1. Fix it!
result
Your answer appears here as a table.
- Sam's group holds one row. Which of its boxes are
NULL, and which are not? c.idis Sam's own id, which is never empty. Count a column from orders instead.- Change COUNT(c.id) to COUNT(o.id), then press run.
nailed it
zero means zero now. :D
- After a
LEFT JOIN,COUNT(*)overcounts empty groups. - Count a column from the right-hand table, like
o.id. SUMof onlyNULLs givesNULL, not 0.
new words in your collection
- overcount
This lesson isn’t saved. A free account remembers every lesson you finish.