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
[...]
```