How do I write a running total in SQL?
Calculate each branch's year-to-date sales, month by month, by adding ORDER BY inside OVER.
Free course. Your Maestro, an AI, .
In a spreadsheet, a running total is a formula you drag down: "this cell plus the one above." SQL has no cell above, but it has windows.
You already wrote SUM(ventas) OVER (PARTITION BY sucursal), which puts the year's total on every row. Add ORDER BY mes inside the OVER and something new happens: each row adds up everything from its branch's first month through itself.
That's because when there's an ORDER BY inside OVER, the frame (the rows of the window that go into the calculation) runs from the start of the partition to the current row. Without ORDER BY, the frame is the whole partition.
SUM(ventas) OVER (
PARTITION BY sucursal
ORDER BY mes
) AS acumuladoRow by row
Here are Centro's first four months, as Maestro's sandbox returned them:
| mes | ventas | acumulado |
|---|---|---|
| 2025-01-01 | 165100 | 165100 |
| 2025-02-01 | 155700 | 320800 |
| 2025-03-01 | 178700 | 499500 |
| 2025-04-01 | 175800 | 675300 |
Think of the frame as a window that stretches. In January, Centro's frame holds a single row, so the running total equals that month's sales. In February, January and February are in. In March, all three. The function is the same SUM as always: what changes from row to row is how many rows it gets. And when the query reaches Norte's first row, the frame starts over from zero, because PARTITION BY keeps it inside its branch.
The chart draws the running total for all three branches. Vega-Lite has windows too: each line comes from a sum whose frame runs from the start to the current row, the same thing you just read in SQL.
Try it
Calculate each branch's running total for the year. Before you write it, answer the question below. If your running total doesn't restart for each branch, ask your Maestro.
Check yourself: Before you run it: what number will the acumulado column show for Centro in December?
Return sucursal, mes, ventas and acumulado: the sales from January through that month, within its branch. Sort by sucursal, mes. You're done when Centro's December shows 2174100.
Your turn
Return sucursal, mes, ventas and acumulado: the sales from January through that month, within its branch. Sort by sucursal, mes. You're done when Centro's December shows 2174100.
Tables: ventas_mensuales (sucursal, mes, ventas)
ventas_mensuales · 35 rows
| Column | Type |
|---|---|
| sucursal Nombre de la sucursal: Centro, Norte o Sur. | VARCHAR |
| mes Primer día del mes que resume la fila. | DATE |
| ventas Ventas del mes, en pesos. | BIGINT |
First rows
| sucursal | mes | ventas |
|---|---|---|
| Centro | 2025-01-01 | 165100 |
| Centro | 2025-02-01 | 155700 |
| Centro | 2025-03-01 | 178700 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
Centro's December shows 2174100: the same year total you saw on the first page. That makes sense: by December the frame covers all 12 months.
Here the inner ORDER BY does more than sort: it changes which rows go into the sum. With SUM, AVG, COUNT and the rest, adding or removing that ORDER BY changes the result.
When the table isn't perfect
An empty value. SUM ignores NULLs, inside a window too. In the sandbox, with three rows worth 100, NULL and 50, the running total came out 100, 100 and 150: the empty row repeats the previous total instead of turning it into NULL. If a NULL in your table means "nobody entered it," that repeated 100 looks like a month with no sales, and nobody will notice.
A missing month. Sur has no June, and nothing strange happens here: July's running total is still everything sold through July. A sum doesn't compare against "the previous month" the way LAG does. What does happen is that your report has 11 rows for Sur, and anyone looking for "the running total through June" won't find it.
Several years. This table covers only 2025. If yours spans several years and you want year-to-date, the year goes in the partition too, so the frame restarts every January:
SUM(ventas) OVER (
PARTITION BY sucursal, year(mes)
ORDER BY mes
) AS acumulado_del_añoOn each row you have the running total, and with OVER () (or PARTITION BY sucursal) you have the total. Divide one by the other and you get the cumulative percentage: how much of the year had been sold by each month. First see it solved for another branch, and try to guess each step before you open it.
Norte's cumulative percentage
Worked example · 4 steps
Using ventas_mensuales: for the Norte branch, each month's running total, the year's total, and what percentage of the year had been sold by the end of each month. In which month did it pass the halfway mark?
Work out the next step yourself, then open it to compare.
Now all three branches at once
In which month did each branch pass half of its sales for the year? This time with no WHERE: all 35 rows in one query, the way a real report needs them. Answer the question below and write the query. If your percentage doesn't reach 100 in December, ask your Maestro.
Check yourself: With no WHERE, the numerator is SUM(ventas) OVER (PARTITION BY sucursal ORDER BY mes). What goes in the denominator so each branch reaches 100 in December?
Return sucursal, mes, ventas, acumulado (within its branch) and pctacumulado: the running total divided by its branch's total for the year, times 100, with ROUND(..., 1). No WHERE: all 35 rows. Sort by sucursal, mes. You're done when December shows 100 for all three branches and
Your turn
Return sucursal, mes, ventas, acumulado (within its branch) and pct_acumulado: the running total divided by its branch's total for the year, times 100, with ROUND(..., 1). No WHERE: all 35 rows. Sort by sucursal, mes. You're done when December shows 100 for all three branches and Centro's July shows 56.2.
Tables: ventas_mensuales (sucursal, mes, ventas)
ventas_mensuales · 35 rows
| Column | Type |
|---|---|
| sucursal Nombre de la sucursal: Centro, Norte o Sur. | VARCHAR |
| mes Primer día del mes que resume la fila. | DATE |
| ventas Ventas del mes, en pesos. | BIGINT |
First rows
| sucursal | mes | ventas |
|---|---|---|
| Centro | 2025-01-01 | 165100 |
| Centro | 2025-02-01 | 155700 |
| Centro | 2025-03-01 | 178700 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
In the run, Centro stood at 48.2 in June and 56.2 in July, and Norte at 48.3 and 56.1. Sur has no June: it reached May at 43.2 and July at 52.3. All three passed the halfway mark in July. Notice that both windows have the same PARTITION BY sucursal and only the numerator has ORDER BY: the same partition, with different frames.
Interviews ask for it in many forms: "calculate each user's cumulative revenue by day," "on what date did each customer reach 10,000 pesos in purchases?" or "which products make up 80% of sales?" All three are a running total with the right partition and order. The last one sorts sales from highest to lowest and cuts where the cumulative percentage passes 80. If you want to practice the 80% one, ask your Maestro to set it up with this data.
If your monthly report has a "year to date" column that someone fills in by hand or with a dragged formula, it's one line of SQL. Add the percentage of the year too, and check with whoever reads it whether a month with no sales should still show up as a row.