Running Totals in Excel Versus Window Functions in SQL

The running balance is the formula people copy down a column without thinking: =SUM($D$2:D2) in row 2, filled down. It works until someone sorts the sheet, inserts a row in the middle or pastes values over part of it. SQL's window functions express the same idea in a way that cannot be broken by layout. This post translates running totals, per-group totals, moving averages, differences and ranks, and explains the one trap that produces totals that look right and are not. It is a spoke of translating Excel formulas into SQL.

The data

WITH orders(order_no, region, placed, amount) AS (
  VALUES
    (1, 'North', DATE '2024-03-01', 120.00),
    (2, 'North', DATE '2024-03-03',  80.00),
    (3, 'South', DATE '2024-03-05',  45.50),
    (4, 'North', DATE '2024-03-09', 200.00),
    (5, 'South', DATE '2024-03-12',  30.00)
)

The running total

In Excel, with orders sorted by date in rows 2 to 6 and amounts in column D, the running total in E2 is =SUM($D$2:D2), copied down. The dollar signs anchor the start of the range so that it grows by one row each step. In SQL:

SELECT order_no, placed, amount,
       sum(amount) OVER (ORDER BY placed, order_no) AS running_total
FROM orders
ORDER BY placed, order_no;

 order_no |   placed   | amount | running_total
----------+------------+--------+---------------
        1 | 2024-03-01 | 120.00 |        120.00
        2 | 2024-03-03 |  80.00 |        200.00
        3 | 2024-03-05 |  45.50 |        245.50
        4 | 2024-03-09 | 200.00 |        445.50
        5 | 2024-03-12 |  30.00 |        475.50

sum(...) OVER (ORDER BY ...) computes the sum of the rows from the start of the ordering up to the current row. The order is stated in the query, so it cannot be broken by sorting the output differently, and there are no cell references to drift. The outer ORDER BY only controls how the result is displayed.

Running totals per group

A running total that restarts for each region is, in Excel, something like =IF(B3=B2, E2+D3, D3) in a sheet sorted by region and date: add to the previous total if the region is unchanged, otherwise start again. It requires the sort and breaks if the sheet is rearranged. In SQL, PARTITION BY:

SELECT region, placed, amount,
       sum(amount) OVER (PARTITION BY region ORDER BY placed, order_no) AS region_running
FROM orders
ORDER BY region, placed, order_no;

 region |   placed   | amount | region_running
--------+------------+--------+----------------
 North  | 2024-03-01 | 120.00 |         120.00
 North  | 2024-03-03 |  80.00 |         200.00
 North  | 2024-03-09 | 200.00 |         400.00
 South  | 2024-03-05 |  45.50 |          45.50
 South  | 2024-03-12 |  30.00 |          75.50

Each region has its own running sum starting from its first row. Any number of groups works, and the data does not need to be sorted to compute the right answer.

The tie trap

The same window machinery drives pivots and ranked summaries, as shown in pivot tables versus GROUP BY and CASE and SUMIF and SUMIFS versus GROUP BY.

There is one behavior of window functions that surprises people coming from spreadsheets. When you write ORDER BY inside the window without saying how far the frame extends, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with equal ordering values as peers and includes all of them in the frame. With ties in the ordering column, the running total jumps:

WITH t(id, d, amount) AS (
  VALUES ('a', DATE '2024-03-01', 10), ('b', DATE '2024-03-01', 20), ('c', DATE '2024-03-02', 5)
)
SELECT id, d, amount,
       sum(amount) OVER (ORDER BY d)                                          AS default_frame,
       sum(amount) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM t
ORDER BY d, id;

 id |     d      | amount | default_frame | rows_frame
----+------------+--------+---------------+------------
 a  | 2024-03-01 |     10 |            30 |         10
 b  | 2024-03-01 |     20 |            30 |         30
 c  | 2024-03-02 |      5 |            35 |         35

Rows a and b share a date, so with the default frame both see the total of both (30), and the running total never shows 10 for a. The ROWS frame counts physical rows and gives 10, 30, 35, which is what a copied spreadsheet formula would give. Two ways to avoid the surprise: add a tie-breaker to the ORDER BY so every row has a unique position (ORDER BY d, id, as in the examples above, which makes the default frame behave like ROWS), or write the frame explicitly. Doing both costs nothing. Without a unique order, the running total for tied rows is also not deterministic about which row comes first; the explicit tie-breaker fixes that as well.

Moving averages

A three-period moving average in Excel is =AVERAGE(D2:D4) copied down, with the first rows handled specially because there are fewer than three values above. In SQL:

SELECT order_no, amount,
       round(avg(amount) OVER (ORDER BY placed, order_no
                               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg3
FROM orders
ORDER BY placed, order_no;

 order_no | amount |  avg3
----------+--------+--------
        1 | 120.00 | 120.00
        2 |  80.00 | 100.00
        3 |  45.50 |  81.83
        4 | 200.00 | 108.50
        5 |  30.00 |  91.83

The frame 2 PRECEDING AND CURRENT ROW takes the current row and up to two before it, so the first row averages only itself and the second averages two values. That is the behavior of a copied formula that has fewer cells at the top; if you want nulls until a full window exists, compute a count over the same frame and return null when it is below three. The frame counts rows, so gaps in time (missing days) are not averaged as zeros. If the average should be over calendar days, use a RANGE frame with an interval, or generate the missing dates first.

Differences from the previous row

=D3-D2 copied down is the change from the previous row. The window function is lag:

SELECT order_no, amount,
       amount - lag(amount) OVER (ORDER BY placed, order_no) AS change
FROM orders
ORDER BY placed, order_no;

 order_no | amount | change
----------+--------+--------
        1 | 120.00 |
        2 |  80.00 | -40.00
        3 |  45.50 | -34.50
        4 | 200.00 | 154.50
        5 |  30.00 | -170.00

The first row has no previous row, so lag returns null, where the spreadsheet usually leaves the cell empty or has a different formula in the top row. lag(amount, 1, 0) gives a default. lead looks forward. Percent change is amount / lag(amount) OVER (...) - 1, guarded against a zero divisor with NULLIF.

Ranks and shares

=RANK(D2, D$2:D$6) ranks each amount among all amounts. In SQL:

SELECT order_no, amount,
       rank() OVER (ORDER BY amount DESC) AS amount_rank,
       round(100 * amount / sum(amount) OVER (), 1) AS pct_of_total
FROM orders
ORDER BY order_no;

 order_no | amount | amount_rank | pct_of_total
----------+--------+-------------+--------------
        1 | 120.00 |           2 |         25.2
        2 |  80.00 |           3 |         16.8
        3 |  45.50 |           4 |          9.6
        4 | 200.00 |           1 |         42.1
        5 |  30.00 |           5 |          6.3

rank() behaves like Excel's RANK: tied values share a rank and the next rank is skipped. dense_rank() does not skip, and row_number() gives every row a distinct number. sum(amount) OVER () with an empty window is the grand total on every row, the SQL for an absolute reference like $D$7. Add PARTITION BY region to rank within each region.

Why this is more reliable than the copied formula

A copied formula depends on three conditions that nothing enforces: the rows are in the right order, no row has been inserted or deleted in the middle, and the formula is present in every row. Sort the sheet, and the running totals are wrong (the range anchored at the top now includes different rows). Insert a row, and it has no formula until someone copies one in. Paste values over part of the column, and the chain breaks. The window function has none of these dependencies, because the order is in the query and the formula is the query. For reports that must be right every time they run, that is the point.

Verify

Compare the last running total with the plain sum(amount): they must be equal (475.50 here). Compare a few intermediate rows with the workbook's cached values. Check the group totals against a GROUP BY query. Test with ties in the ordering column and with a group that has one row.

When a copied running total is fine

The sheet is a small personal ledger. A cash book with sixty rows, entered in order, does not need a database.

The rows are only ever appended. If nobody sorts or inserts, the copied formula is safe, and it is clearer to a person reading the sheet.

The calculation is part of an interactive model. A running balance that updates as the user types is the spreadsheet's strength.

There is no database. Loading data to compute a sum is only worth it if more is coming.

PostgreSQL documents window functions in its tutorial and the window function reference, and Excel's RANK in Microsoft's function reference. To check a small extract as JSON, use the CSV to JSON converter.