sql · level 2 · unit 1 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
Group by two columns for one row per .
GROUP BY player gives one row per player. GROUP BY player, game goes finer: one group for every combination of player and game in the table, like Mia with darts and Mia with bowling.
One rule keeps reports honest. Every plain column in SELECT must also be in GROUP BY. Everything else must sit inside an aggregate.
Break the rule and SQLite does not complain. It quietly takes the value from one row of the group, which is often the wrong one.
SELECT player, game, COUNT(*) AS rounds, SUM(points) AS total
FROM rounds
GROUP BY player, game
ORDER BY player, game;ORDER BY player, game sorts by player first, then by game within each player.
guess first
Best rounds by game and player. How many rows?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT game, player, MAX(points) AS best
FROM rounds
GROUP BY game, player
ORDER BY game, best DESC;your turn
Your turn. Pick an aggregate for each pairing.
Type SUM, AVG, MIN or MAX in the blank. You get one answer for every player and game combination.
result
Your answer appears here as a table.
fix it
Every player shows darts only. Fix it!
result
Your answer appears here as a table.
- Count the rows. Should there be one per player, or one per player and game?
gameis a plain column inSELECT, so it belongs inGROUP BYtoo.- Change GROUP BY player to GROUP BY player, game, then press run.
nailed it
you grouped inside groups. :D
GROUP BY a, bmakes one group per combination.- Every plain column in
SELECTbelongs inGROUP BY. - Everything else goes inside an aggregate.
new words in your collection
- combination
This lesson isn’t saved. A free account remembers every lesson you finish.