sql · level 2 · unit 1 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
gives one answer for every .
So far, COUNT(*) has squashed a whole table into one number. Often you want one number for each kind of thing instead: how many cats, how many dogs, how many fish.
GROUP BY species sorts the rows into piles, one group for each species. The aggregate then runs once inside every group, so you get one row back per group.
Put the grouping column in the SELECT too. Without it you get the numbers, but no way to tell which group each one belongs to.
SELECT species, COUNT(*) AS pets
FROM pets
GROUP BY species;SQLite tends to hand groups back from A to Z, but only ORDER BY promises an order.
guess first
Snacks by kind. What comes back?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT kind, SUM(stock) AS stock
FROM snacks
GROUP BY kind;your turn
Your turn. Pick how to group the scores.
Type player or game in both blanks, the same word twice. You get how many scores each group has and its best one.
result
Your answer appears here as a table.
fix it
This should count each species, but gives one row. Fix it!
result
Your answer appears here as a table.
- Count the rows that came back. You wanted one for each species.
- Without
GROUP BY,COUNT(*)squashes the whole table into a single row. - Add GROUP BY species before the semicolon, then press run.
nailed it
you split a table into groups. :D
GROUP BYmakes one group for each value in a column.- Aggregates run once inside every group.
- Put the grouping column in
SELECTto label each answer.
new words in your collection
- GROUP BY
- group
This lesson isn’t saved. A free account remembers every lesson you finish.