How do you compute a running total in SQL?
You use the sum function inside a window, with an order and a frame that says "up to this row".
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
placed_on TEXT NOT NULL
);
-- The running total of the amount, in date order.
SELECT
id,
placed_on,
amount,
SUM(amount) OVER (
ORDER BY placed_on, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY placed_on, id;
So what are the parts? The function SUM is the same one that you use in a group by. The OVER with an ORDER BY says that the rows are in that order. And the frame says which of those rows the function looks at.
The frame is the part that people forget. Written out, it means "the rows from the very start up to and including the current row". Without the frame the default would be slightly different, because the default includes every row that has the same order value as the current one, and that matters when two rows have the same date.
There is one more thing to know. A running total needs a total order, otherwise two rows on the same day have no order between them and the running total for the second one is not decided. That is why the order in the window usually ends with the identifier, as it does above.
The next question in this topic puts it to work.