Running Total
Part of the Beyond the Basics section of Coddy's SQL journey — lesson 25 of 27.
A running (cumulative) total is SUM over a window with ORDER BY: that turns the aggregate into one that grows row-by-row.
SELECT day, amount,
SUM(amount) OVER (ORDER BY day) AS running_total
FROM revenueWithout ORDER BY the window covers every row and you'd get the grand total on every line. With it, the window expands one row at a time in that order, so each row sees only itself and everything before it.
Add a PARTITION BY to reset the running total per group, e.g. running total of revenue per region, day-by-day.
Challenge
EasyAvailable tables and columns:
<strong>revenue</strong>:<strong>region</strong>,<strong>day</strong>,<strong>amount</strong>
Return region, day, amount, and region_running_total: the cumulative amount for that region up to and including this day. The running total should reset for each region.
Use PARTITION BY on the window. Order by region, then day.
Cheat sheet
A running (cumulative) total uses SUM with ORDER BY in the window — the window expands row-by-row in that order:
SELECT day, amount,
SUM(amount) OVER (ORDER BY day) AS running_total
FROM revenueAdd PARTITION BY to reset the running total per group:
SELECT region, day, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY day) AS region_running_total
FROM revenue
ORDER BY region, dayTry it yourself
SELECT region, day, amount,
-- SUM(amount) OVER (PARTITION BY region ORDER BY day)
FROM revenue
ORDER BY region, day
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Beyond the Basics
6Analytics Recipes
Top-N per GroupPercentage of TotalRunning TotalKeeping One Row per KeyRecap - Top Customers