AI Hints
Homeโ€บSQL Tutorialโ€บRunning Totals and Trends
SQL ยท Lesson 66 of 126

Running Totals

Adding up as you go.

โฑ 2 min readโ–ถ Video at 1:33:50๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Helen has a target. One thousand orders in twenty twenty five. At the end of each month, she wants to know, how many orders so far this year? That's a running total. Each month adds its orders to everything before it.

Running Totals
From the video: Running Totals
  • Target: 1,000 orders in 2025
  • How many so far?
  • A running total

SUM OVER ORDER BY

The trick is sum, with over, and order by month. When a window has an order by, the sum only adds rows up to the current one. So each month's total includes every month before it. Run it. Helen passed one thousand orders in December, finishing on one thousand and thirty nine.

query.sqlsql
WITH monthly AS (
    SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY month
)
SELECT month,
       orders,
       SUM(orders) OVER (ORDER BY month) AS running_total
FROM monthly;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  month  โ”‚ orders โ”‚ running_total โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2025-01 โ”‚ 69     โ”‚ 69            โ”‚
โ”‚ 2025-02 โ”‚ 75     โ”‚ 144           โ”‚
โ”‚ 2025-03 โ”‚ 61     โ”‚ 205           โ”‚
โ”‚ 2025-04 โ”‚ 73     โ”‚ 278           โ”‚
โ”‚ 2025-05 โ”‚ 103    โ”‚ 381           โ”‚
โ”‚ 2025-06 โ”‚ 88     โ”‚ 469           โ”‚
โ”‚ 2025-07 โ”‚ 77     โ”‚ 546           โ”‚
โ”‚ 2025-08 โ”‚ 120    โ”‚ 666           โ”‚
โ”‚ 2025-09 โ”‚ 93     โ”‚ 759           โ”‚
โ”‚ 2025-10 โ”‚ 93     โ”‚ 852           โ”‚
โ”‚ 2025-11 โ”‚ 83     โ”‚ 935           โ”‚
โ”‚ 2025-12 โ”‚ 104    โ”‚ 1039          โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Restart each year

Add partition by year, and the running total starts again at zero every January. That's called year to date. Notice we filter to the last three months in a second step. If we filtered first, the window would only see those three months. Run it. By December twenty twenty four, Helen had five hundred and fifty nine orders for the year. By December twenty twenty five, one thousand and thirty nine.

query.sqlsql
WITH monthly AS (
    SELECT strftime('%Y', order_date) AS year,
           strftime('%m', order_date) AS month,
           COUNT(*) AS orders
    FROM orders
    GROUP BY year, month
),
ytd AS (
    SELECT year, month, orders,
           SUM(orders) OVER (PARTITION BY year ORDER BY month) AS year_to_date
    FROM monthly
)
SELECT * FROM ytd
WHERE month IN ('10', '11', '12');
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ year โ”‚ month โ”‚ orders โ”‚ year_to_date โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2024 โ”‚ 10    โ”‚ 59     โ”‚ 440          โ”‚
โ”‚ 2024 โ”‚ 11    โ”‚ 54     โ”‚ 494          โ”‚
โ”‚ 2024 โ”‚ 12    โ”‚ 65     โ”‚ 559          โ”‚
โ”‚ 2025 โ”‚ 10    โ”‚ 93     โ”‚ 852          โ”‚
โ”‚ 2025 โ”‚ 11    โ”‚ 83     โ”‚ 935          โ”‚
โ”‚ 2025 โ”‚ 12    โ”‚ 104    โ”‚ 1039         โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Sum, over, order by makes a running total. Add partition by to restart it for each group. Next, comparing each row with the one before.

Key takeaways
  • SUM() OVER (ORDER BY ...)
  • Adds up as it goes
  • PARTITION BY restarts it
Next up: LAG and LEAD โ€” looking at the previous row