sql · level 2 · unit 4 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A subquery in SELECT puts the same value on every row.
Each player's points are interesting. Each player's share of all the points is more interesting. For that you need two numbers on every row: the player's total and the grand total.
A subquery can sit in SELECT like a column. (SELECT SUM(points) FROM scores) gives the grand total, 535, on every row.
Multiply by 100 and divide by the grand total for a . Write 100.0, not 100, so the division keeps its decimals, then ROUND the answer.
SELECT player, SUM(points) AS total,
ROUND(100.0 * SUM(points) / (SELECT SUM(points) FROM scores), 1) AS share
FROM scores
GROUP BY player
ORDER BY total DESC, player;The subquery ignores the grouping. It always looks at the whole table.
guess first
Each kind's share of the stock. What does fruit get?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT kind, ROUND(100.0 * SUM(stock) / (SELECT SUM(stock) FROM snacks)) AS share
FROM snacks
GROUP BY kind
ORDER BY share DESC;your turn
Your turn. Each genre's share of the films.
Type how many decimal places to keep in the blank: 0, 1 or 2. You see what percentage of all the films each genre makes up.
result
Your answer appears here as a table.
fix it
The shares lost their decimals. Fix it!
result
Your answer appears here as a table.
- Every share is a whole number, even with
ROUND(..., 1). Where did the decimals go? - Whole numbers divide to whole numbers, so the decimals were thrown away before
ROUNDsaw them. - Change 100 to 100.0, then press run.
nailed it
you compared each group with the whole. :D
- A subquery in
SELECTgives every row the same value. - A share is 100.0 times the part, divided by the whole.
- Write
100.0so the decimals survive the division.
new words in your collection
- percentage
This lesson isn’t saved. A free account remembers every lesson you finish.