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