How to Calculate Cumulative Sum-Running Total in MySQL

In this tutorial, we will explore how to calculate the cumulative sum, also known as a running total, in MySQL. A cumulative sum is a running total of a dataset that adds each successive value to the total sum of the previous values. This is a common requirement in business analytics, financial reports, and other applications.

What is a Cumulative Sum?

A cumulative sum is a running total that is updated with each new row of data. In simpler terms, for a given set of numbers, the cumulative sum is the sum of all values up to the current row. For example, for a set of values [2, 4, 6], the cumulative sum would be [2, 6, 12], where each value is the total of all previous values added together.

Example Scenario

Suppose you have a table called sales that contains daily sales amounts:

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    sale_amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, sale_amount) VALUES
('2025-01-01', 100.00),
('2025-01-02', 150.00),
('2025-01-03', 200.00),
('2025-01-04', 250.00);
                                

Using SQL Window Functions for Running Totals

The most efficient way to calculate a running total in MySQL is by using the SUM() window function in combination with the OVER() clause. Here's an example query that calculates the cumulative sum of sale_amount for each day:

SELECT sale_date,
       sale_amount,
       SUM(sale_amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;
                                

This query will return a result set with the daily sales amounts and their corresponding cumulative totals:

+------------+------------+--------------+
| sale_date | sale_amount | running_total |
+------------+------------+--------------+
| 2025-01-01 | 100.00     | 100.00       |
| 2025-01-02 | 150.00     | 250.00       |
| 2025-01-03 | 200.00     | 450.00       |
| 2025-01-04 | 250.00     | 700.00       |
+------------+------------+--------------+
                                

Explanation

In the query above, we used the SUM() function with the OVER() clause. The ORDER BY sale_date ensures that the rows are processed in chronological order, and the SUM() function computes the cumulative sum of sale_amount.

Alternative Approach: Using Variables

Before window functions were available in MySQL, one way to calculate a running total was by using session variables. Here's an example query using variables to calculate the cumulative sum:

SET @running_total = 0;

SELECT sale_date,
       sale_amount,
       @running_total := @running_total + sale_amount AS running_total
FROM sales
ORDER BY sale_date;
                                

This approach simulates the behavior of a running total by storing the cumulative sum in a session variable, which is updated with each row processed by the query.

Conclusion

In this tutorial, we learned two ways to calculate cumulative sums (running totals) in MySQL: using the SUM() window function and using session variables. The window function approach is preferred due to its simplicity and efficiency, but both methods are valid depending on your MySQL version and use case.