Running totals in SQL

An ordered window turns a column of amounts into a cumulative curve.

SELECT
    day,
    amount,
    SUM(amount) OVER (
        ORDER BY day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(amount) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_week
FROM daily_revenue;

How it works

1. The frame clause states which rows feed each result.
2. UNBOUNDED PRECEDING to CURRENT ROW gives a running sum.
3. The same window can drive several expressions.

Keywords and builtins used here

• AND
• AS
• AVG
• BETWEEN
• BY
• CURRENT
• FROM
• ORDER
• ROW
• ROWS
• SELECT
• SUM
• day

The run, in numbers

Lines

12

Characters to type

231

Tokens

47

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 30 seconds.

Type this snippet

Step 3 of 9 in Window functions, step 9 of 24 in Analytics & reporting.

← Previous Next →