Running Totals and Cumulative Sums

The Running Balance

The CFO wanted a report showing how the bank balance grew with each deposit. This is a **running total**.

The Syntax

SELECT

transaction_date,

amount,

SUM(amount) OVER (ORDER BY transaction_date) AS running_balance

FROM transactions;

How It Works

By default, `SUM() OVER (ORDER BY ...)` calculates from the first row up to the current row. This is the "Frame."

Explicit Frame Clause

You can define the frame explicitly:

SUM(amount) OVER (

ORDER BY transaction_date

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) AS running_balance

Pro Tip

The frame clause also enables moving averages:

AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS 7_day_avg

*Day 65: First and Last Values in a Window.*