Examples of Compute a Running Total in MySQL
Example 1: Using Variables
Create your_table:
CREATE TABLE your_table (
id INT AUTO_INCREMENT PRIMARY KEY,
value INT
);
INSERT INTO your_table (value) VALUES (10), (20), (30), (40), (50);
SET @running_total := 0;
SELECT id, value, (@running_total := @running_total + value) AS running_total
FROM your_table
ORDER BY id;
Output:
id | value | running_total |
---|---|---|
1 | 10 | 10 |
2 | 20 | 30 |
3 | 30 | 60 |
4 | 40 | 100 |
5 | 50 | 150 |
Explanation: The output displays the id and value columns from the your_table table, along with a running_total column. The running_total column shows the cumulative sum of value, calculated incrementally by row.
Example 2: Using Subqueries
SELECT id, value, (
SELECT SUM(value)
FROM your_table t2
WHERE t2.id <= t1.id
) AS running_total
FROM your_table t1
ORDER BY id;
Output:
id | value | running_total |
---|---|---|
1 | 10 | 10 |
2 | 20 | 30 |
3 | 30 | 60 |
4 | 40 | 100 |
5 | 50 | 150 |
Explanation: The output presents id, value, and running_total. The running_total column accumulates the sum of value up to each row, calculated by summing values with id less than or equal to the current row’s id.
Example 3: Using JOINs
SELECT t1.id, t1.value, SUM(t2.value) AS running_total
FROM your_table t1
JOIN your_table t2 ON t2.id <= t1.id
GROUP BY t1.id, t1.value
ORDER BY t1.id;
Output:
id | value | running_total |
---|---|---|
1 | 10 | 10 |
2 | 20 | 30 |
3 | 30 | 60 |
4 | 40 | 100 |
5 | 50 | 150 |
Explanation: The query utilizes a self-join to compute the running total. For each row in your_table, it sums the value column from all rows with IDs less than or equal to the current row’s ID. Results are grouped by id and value, displaying the running total.
How to Compute a Running Total in MySQL
Computing running totals is a common task that involves calculating the cumulative sum of the values over a specified sequence or range. MySQL a popular relational database management system provides powerful features for performing such computations efficiently.
In this article, we’ll explore how to calculate running totals in MySQL using various techniques and approaches.