sql · level 3 · unit 2 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A adds a group answer without squashing rows.
GROUP BY squashes rows: ten orders become one total. Sometimes you want to keep every order and see the total beside it. A window function does that. It looks across many rows, but it gives an answer on every row.
Add after an aggregate to turn it into a window function. Empty brackets, OVER (), make the window the whole result. So SUM(total) OVER () puts the grand total beside every order.
With the total on each row, you can compare across rows, such as each order's share of the takings.
SELECT id, total,
SUM(total) OVER () AS all_orders,
ROUND(100.0 * total / SUM(total) OVER (), 1) AS pct
FROM orders
ORDER BY id;The window sees the rows left after WHERE, not the whole table.
guess first
Only York customers. What does the window count?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT name, city,
COUNT(*) OVER () AS everyone
FROM customers
WHERE city = 'York'
ORDER BY name;your turn
Your turn. Work out each order's share.
Fill the blank with a window function that gives the grand total of every order, so pct becomes each order's percentage of the takings.
result
Your answer appears here as a table.
fix it
Ten orders went in, one row came out. Fix it!
result
Your answer appears here as a table.
- Only one row came back. What does an aggregate do to rows when there is no window?
- Without
OVER,SUMsquashes every row into one. WithOVER ()it keeps them all. - Add OVER () after SUM(total) on line 2, then press run.
nailed it
your totals sit beside every row now. :D
- A window function gives an answer on every row.
OVER ()makes the window the whole result.- Without
OVER, an aggregate squashes the rows into one.
new words in your collection
- window function
- OVER
This lesson isn’t saved. A free account remembers every lesson you finish.