Calculate a running total in SQL

This post is an illustrating example for the article on SQL window functions.

A running total is the sum of a sequence of numbers that is updated every time a new number is added to the sequence.

It looks like this:

Amount	Running Total
2	2
7	9
19	28
13	41
4	45
9	54

To illustrate how to calculate a running total using SQL, I will use the DVDrental database that can be downloaded here.

Lets say, that I want to get the running total of the daily payments.

First, I create a CTE that sums up the daily payment amounts:

WITH daily_amount AS (
    SELECT 
        payment_date::date
        , SUM(amount) AS amount
    FROM 
        payment
    GROUP BY
        payment_date::date
)

Then, I use a window function on the amount column:

SUM(amount) OVER(ORDER BY payment_date ROWS UNBOUNDED PRECEDING) AS running_total

I use the ORDER BY statement to make sure that the sequence of payment amounts is in chronological order. Then I use the ROWS UNBOUNDED PRECEDING statement to include all the rows that are previous to the current row when I calculate the sum.

The final query looks like this:

WITH daily_amount AS (
    SELECT 
        payment_date::date
        , SUM(amount) AS amount
    FROM 
        payment
    GROUP BY
        payment_date::date
)
SELECT 
    payment_date::date
    , amount
    , SUM(amount) OVER(ORDER BY payment_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM 
    daily_amount
;

It returns the following result set:

 payment_date | amount  | running_total 
--------------+---------+---------------
 2007-02-14   |  116.73 |        116.73
 2007-02-15   | 1188.92 |       1305.65
 2007-02-16   | 1154.18 |       2459.83
 2007-02-17   | 1188.17 |       3648.00
 2007-02-18   | 1275.98 |       4923.98
 2007-02-19   | 1290.90 |       6214.88
 2007-02-20   | 1219.09 |       7433.97
[...]