How to Generate a Running(Accumulated) Total in SQL

Have you ever needed to calculate a “running total” or “cumulative sum” in SQL?

You can do this in SQL without any other programming language to process the data.

What is a Running Total?

A running total, or cumulative sum, is a value that indicates the total of a value in the current row and all rows above it.

It can be used across a span of time, to find the total order value so far for an eCommerce site. Or, it could be used in a sports website to display the team’s score so far for every score that is made.

There are many uses for it, and in this guide we’ll see how to calculate it.

Sample Data

We’ll use some sample data to demonstrate the concept of an SQL running total.

Here’s our sample table called sales_history, which represents orders and the value of orders that have been made over time.

order_id	sales_date	salesperson	order_value
1	01 Aug 2021	John	15
2	01 Aug 2021	Sarah	8
3	01 Aug 2021	Sally	19
4	02 Aug 2021	John	2
5	02 Aug 2021	Mark	18
6	03 Aug 2021	Sally	3
7	03 Aug 2021	Mark	21
8	03 Aug 2021	Sarah	16
9	04 Aug 2021	John	4

Here’s the SQL to create the table. This will work in MySQL but you can adjust the data types for other databases.

CREATE TABLE sales_history (
  order_id INT,
  sales_date DATE,
  salesperson VARCHAR(20),
  order_value INT
);

 

INSERT INTO sales_history
(order_id, sales_date, salesperson, order_value) VALUES
(1, '20210801', 'John', 15),
(2, '20210801', 'Sarah', 8),
(3, '20210801', 'Sally', 19),
(4, '20210802', 'John', 2),
(5, '20210802', 'Mark', 18),
(6, '20210803', 'Sally', 3),
(7, '20210803', 'Mark', 21),
(8, '20210803', 'Sarah', 16),
(9, '20210804', 'John', 4);

If we want to select data from this table, our query would look like this:

SELECT
order_id,
sales_date,
salesperson,
order_value
FROM sales_history;

How can we generate a running total?

Generate a Running Total in SQL with a Window Function

To generate a running total in SQL, we can use something called a “window function”.

A window function is a way to write a function so it looks over a range of records, or a “window”, instead of all records or a single record.

In this case, we can use a window function to look at the SUM of the number of sales. Our “window” or range of data to look at would be all of the rows that have come before this.

For our example, we can use the SUM function to calculate the running total.

The SUM function, written as a window function, looks like this:

SUM (expression)
OVER (
  [ PARTITION BY partition_clause ]
  [ ORDER BY order_clause ]
)

This has a few components:

The expression is the column we want to sum.
The partition_clause specifies the range of data to use
The order_clause specifies how data is ordered in the range

Let’s see an example of this.

SELECT
order_id,
sales_date,
salesperson,
order_value,
SUM(order_value)
  OVER (ORDER BY order_id ASC) AS running_total
FROM sales_history
ORDER BY order_id ASC;

Run this query, and you’ll see these results:

order_id	sales_date	salesperson	order_value	running_total
1	01 Aug 2021	John	15	15
2	01 Aug 2021	Sarah	8	23
3	01 Aug 2021	Sally	19	42
4	02 Aug 2021	John	2	44
5	02 Aug 2021	Mark	18	62
6	03 Aug 2021	Sally	3	65
7	03 Aug 2021	Mark	21	86
8	03 Aug 2021	Sarah	16	102
9	04 Aug 2021	John	4	106

Awesome! We’ve used the SUM function as a window function to calculate the running total.

Generating a Running Total In Groups

Another scenario for generating a running total is to see a running total in different groups.

Perhaps you want to see a running total of sales per month, or scores in a match by each person, or something else.

You can do this with a slight modification of the SUM function.

In the OVER clause, another clause to add is the partition by clause. This allows us to specify groups when calculating the SUM value.

This means, for our data, we could see the running total of orders for each salesperson.

To do this, we partition by the salesperson in our query:

SELECT
order_id,
sales_date,
salesperson,
order_value,
SUM(order_value) OVER (
  PARTITION BY salesperson
  ORDER BY order_id ASC) AS running_total
FROM sales_history
ORDER BY order_id ASC;

If we run this query, this is what we’ll see:

order_id	sales_date	salesperson	order_value	running_total
1	01 Aug 2021	John	15	15
2	01 Aug 2021	Sarah	8	8
3	01 Aug 2021	Sally	19	19
4	02 Aug 2021	John	2	17
5	02 Aug 2021	Mark	18	18
6	03 Aug 2021	Sally	3	22
7	03 Aug 2021	Mark	21	39
8	03 Aug 2021	Sarah	16	24
9	04 Aug 2021	John	4	21

Filter on a Running Total

Another scenario you might see when working with running totals is the need to filter on it.

How can you filter on this data? Let’s say you want to see all orders where the running total is greater than 20.

We could try this query, using the WHERE clause:

We leave the ORDER BY on the outside of the query as that’s how the data is ordered.

Now, we can add a where clause on the running total, because here it’s a derived column (from the subquery) and not a window function.

SELECT
order_id,
sales_date,
salesperson,
order_value,
running_total
FROM (
  SELECT
  order_id,
  sales_date,
  salesperson,
  order_value,
  SUM(order_value) OVER (
    PARTITION BY salesperson
    ORDER BY order_id ASC) AS running_total
  FROM sales_history
) sub
WHERE running_total > 20
ORDER BY salesperson ASC, order_id ASC;

Run this query and this is the result we’ll see:

order_id	sales_date	salesperson	order_value	running_total
9	04 Aug 2021	John	4	21
7	03 Aug 2021	Mark	21	39
6	03 Aug 2021	Sally	3	22
8	03 Aug 2021	Sarah	16	24

We’ve filtered the results based on the running total value by enclosing the query inside a subquery and putting the WHERE clause outside the subquery.

Only those rows with a running total value of greater than 20 are shown.

Conclusion

So, that’s how to calculate a running total in SQL. This technique (using SUM as a window function) should work in Oracle, SQL Server, MySQL, and PostgreSQL, and many other databases too.

credit: https://www.databasestar.com/sql-running-total