Free SQL queries lessonsSQL queries · Stage 4 · Subqueries and window functions · Step 5

Running totals and the row before

Accumulate with SUM over an ordered window, and compare a row with the previous one using LAG. About 10 minutes.

A total that grows

A bank statement shows the balance after every entry, not just the final one. That's a running total: for each row, the sum of everything up to and including that row.

Use SUM as a window function and give it an ORDER BY. The order says what "so far" means. Add PARTITION BY to restart the total for each group, such as each account.

SUM(x) OVER (ORDER BY d): everything up to this row.

In code

SELECT day, amount,
  SUM(amount) OVER (ORDER BY day)
    AS balance
FROM entries;

Balance after each entry

entries

day	amount
1	100
2	-30
3	200
4	-50

balance: 100

Day 1: only this row so far. Balance 100.

Move 1 of 4

Ties, and looking back

Careful with ties. Rows with the same value in ORDER BY count as one lump, so two entries on the same day both show the total after both. Add a unique column, such as the id, after the day to make it row by row.

LAG(column) returns the value from the previous row in the window, and NULL for the first row. Subtract it to get a change since the last row. LEAD looks forward instead.

Same day, two rows: add an id to ORDER BY, or they share a total.

In code

SELECT day, temp,
  temp - LAG(temp)
    OVER (ORDER BY day) AS change
FROM readings;

Quick check

Amounts are 10, 20 and 30 in day order. What does SUM(amount) OVER (ORDER BY day) show on the third row?

Show the answer

60. 10 + 20 + 30: everything up to and including the third row.

Your problem

Running balance

The entries table has one row per bank entry, with the day it happened and an amount (negative for money out). Show each entry's id, day and amount, and a balance column that is the total of all amounts up to and including that entry, counting entries in order of day and then id. Order the answer by day, then id.

Tables

entries(id, day, amount)

Up to 2,000 entries

Write the query in your browserFree account, no card. Your query runs on real tables, with hints if you get stuck, and your progress is saved.