Back to Developer Roadmap

Running Total

src/data/question-groups/sql-queries/content/running-total.md

4.01.2 KB
Original Source

Let's use a table Sales as a reference for this query. It has three columns: id, day which represents the day of the week, and amount which is the amount sold in US Dollars. The table looks like this:

iddayamount
1Monday200
2Tuesday300
3Wednesday600
4Thursday390
5Friday900

The query to calculate the running total is:

sql
SELECT
  id,
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM
  sales;

The query uses a Window function OVER to sum the amount for each row of data and saving the running total. It gets the total for each day and adds it to the previous totals. The result of the query looks like this:

iddayamountrunning_total
1Monday200200
2Tuesday300500
4Thursday3901100
3Wednesday6001490
5Friday9002390

You can observe from the image that the last column is running_total, which takes the amount for the current day and adds it to its previous value to get its current value.