sql · level 3 · unit 3 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
SUM(...) OVER (ORDER BY day) builds a .
A running total grows as you go: the first day's takings, then the first two days together, and so on. It answers questions like how much the café has made so far this week.
Put ORDER BY inside OVER and the sum stops covering the whole result. Each row now adds itself to every row before it in that order. SUM(amount) OVER (ORDER BY day) gives the takings so far on each day.
Add PARTITION BY too, and every group keeps its own running total, starting from zero.
SELECT day, amount,
SUM(amount) OVER (ORDER BY day) AS so_far
FROM takings
ORDER BY day;Rows that tie in the window's ORDER BY are added in together, so give every row its own place.
guess first
Two stalls, two days. What are the running totals?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT stall, day, amount,
SUM(amount) OVER (PARTITION BY stall ORDER BY day) AS so_far
FROM sales
WHERE day <= '2024-06-02'
ORDER BY stall, day;your turn
Your turn. Track each customer's spending.
Fill the blank so every customer gets their own running total, growing with each order they place.
result
Your answer appears here as a table.
fix it
Two orders, one running total. Fix it!
result
Your answer appears here as a table.
- Look at orders 1 and 2. Why do they show the same running total?
- Rows that tie in the window's
ORDER BYare added in together, and both orders are on 1 May. - Change ORDER BY day to ORDER BY day, id inside OVER, then press run.
nailed it
you kept a tally that grows by itself. :D
SUM(...) OVER (ORDER BY ...)gives a running total.- Rows that tie in the window's order are added in together.
- Add
PARTITION BYfor a separate running total per group.
new words in your collection
- running total
This lesson isn’t saved. A free account remembers every lesson you finish.