sql · level 2 · unit 5 · lesson 3
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
Turn a test into a 1 or a 0, then SUM it.
For each game, how many scores were 100 or more, and how many were under 50? WHERE cannot help, because each count needs different rows, side by side.
So give every row a . CASE WHEN points >= 100 THEN 1 ELSE 0 END is 1 for a big score and 0 for any other. The SUM of the flags is how many rows passed.
Each column gets its own CASE, so one query can count as many things as you like, group by group.
SELECT game,
SUM(CASE WHEN points >= 100 THEN 1 ELSE 0 END) AS big,
SUM(CASE WHEN points < 50 THEN 1 ELSE 0 END) AS small,
COUNT(*) AS played
FROM scores
GROUP BY game
ORDER BY game;COUNT(CASE WHEN points >= 100 THEN 1 END) works too, because COUNT skips the NULLs that a missing ELSE gives.
guess first
A stock check by kind. What comes back?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT kind,
SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END) AS sold_out,
SUM(CASE WHEN stock < 10 THEN 1 ELSE 0 END) AS running_low
FROM snacks
GROUP BY kind
ORDER BY kind;your turn
Your turn. Old films and new films.
Type the same year, like 2020, in both blanks. Each genre then shows how many of its films came out before that year, and how many since.
result
Your answer appears here as a table.
fix it
Darts should show 0 big scores, not NULL. Fix it!
result
Your answer appears here as a table.
- No darts score is 100 or more. What does the
CASEgive for those rows? - With no
ELSE, failing rows getNULL, and theSUMof onlyNULLs isNULL. - Add ELSE 0 before END, then press run.
nailed it
you counted many things at once. :D
- A flag is 1 when a row passes a test, else 0.
- The
SUMof the flags counts the rows that passed. - Give the flag
ELSE 0, or empty groups showNULL.
new words in your collection
- flag
This lesson isn’t saved. A free account remembers every lesson you finish.