Using a correlated query:
SELECT t.id,
t.count,
(SELECT SUM(x.count)
FROM TABLE x
WHERE x.id <= t.id) AS cumulative_sum
FROM TABLE t
ORDER BY t.id
Using MySQL variables:
SELECT t.id,
t.count,
@running_total := @running_total + t.count AS cumulative_sum
FROM TABLE t
JOIN (SELECT @running_total := 0) r
ORDER BY t.id
Note:
- The
JOIN (SELECT @running_total := 0) ris a cross join, and allows for variable declaration without requiring a separateSETcommand. - The table alias,
r, is required by MySQL for any subquery/derived table/inline view
Caveats:
- MySQL specific; not portable to other databases
- The
ORDER BYis important; it ensures the order matches the OP and can have larger implications for more complicated variable usage (IE: psuedo ROW_NUMBER/RANK functionality, which MySQL lacks)
Top answer 1 of 9
119
Using a correlated query:
SELECT t.id,
t.count,
(SELECT SUM(x.count)
FROM TABLE x
WHERE x.id <= t.id) AS cumulative_sum
FROM TABLE t
ORDER BY t.id
Using MySQL variables:
SELECT t.id,
t.count,
@running_total := @running_total + t.count AS cumulative_sum
FROM TABLE t
JOIN (SELECT @running_total := 0) r
ORDER BY t.id
Note:
- The
JOIN (SELECT @running_total := 0) ris a cross join, and allows for variable declaration without requiring a separateSETcommand. - The table alias,
r, is required by MySQL for any subquery/derived table/inline view
Caveats:
- MySQL specific; not portable to other databases
- The
ORDER BYis important; it ensures the order matches the OP and can have larger implications for more complicated variable usage (IE: psuedo ROW_NUMBER/RANK functionality, which MySQL lacks)
2 of 9
99
If performance is an issue, you could use a MySQL variable:
set @csum := 0;
update YourTable
set cumulative_sum = (@csum := @csum + count)
order by id;
Alternatively, you could remove the cumulative_sum column and calculate it on each query:
set @csum := 0;
select id, count, (@csum := @csum + count) as cumulative_sum
from YourTable
order by id;
This calculates the running sum in a running way :)
PopSQL
popsql.com › learn-sql › mysql › how-to-calculate-cumulative-sum-running-total-in-mysql
How to Calculate Cumulative Sum-Running Total in MySQL - PopSQL
Before MySQL version 8 you can use variables for this: SELECT t.day, t.rental_count, @running_total:=@running_total + t.rental_count AS cumulative_sum FROM ( SELECT date(rental_date) as day, count(rental_id) as rental_count FROM rental GROUP BY day ) t JOIN (SELECT @running_total:=0) r ORDER BY t.day; day | rental_count | cumulative_sum -----------+--------------+---------------- 2005-05-24 | 8 | 8 2005-05-25 | 137 | 145 2005-05-26 | 174 | 319 2005-05-27 | 166 | 485 2005-05-28 | 196 | 681
05:33
SQL Tutorial: How to Calculate Cumulative Sum (Running Total) in ...
Calculate rolling sum with SQL
04:45
MySQL Sum Over Analytic Window Function | Cumulative Sum Example ...
09:12
MySQL | Computation of running total and moving average - YouTube
01:21
MySQL : Creating a cumulative sum column in MySQL - YouTube
15:29
MySQL Running Total || how to calculate Running Totals and Cumulative ...
Reddit
reddit.com › r/sql › cumulative sum or running sum
r/SQL on Reddit: Cumulative sum or running sum
March 10, 2023 -
Hello!
I am trying to write a SQL query using a combination of CTE and windows function to calculate a cumulative sum of the count of product.
My apologize as I'm using my mobile but here is an example of the dataset
Productcode RATE1 RATE1 RATE3 RATE1 RATE3 RATE2
The original table has a lot of columns but I'm only interested into 1. It also has customer id but a customer can only have 1 product. There is also date row but I'm not interested in the date
I wrote the below with the idea of taking a column out of many from the original dataset, group the product code and get individual count of each rate and finally add a column running a cumulative sum
WITH testtable AS (Select Productcode ,Count(*) as Freq From table group by 1) Select Productcode ,Sum(Freq) over (partition by productcode) as cumul From testtable
The output is essentially a duplicate of the column Freq as opposed to the cumulative Freq as I hoped.
I also appreciate that there is certainly an easier way of getting this done but I still would like to know why it is not working?
Thanks for your inputs!
Edit: moved 'AS' to the correct position Edit 2: sample dataset and further clarifications Edit 3: solved SUM(COLUMN) OVER (ORDER BY productid)
Top answer 1 of 4
4
I think for it to be a running Sum you need ORDER BY in your parenthesis. SUM(COLUMN) OVER (ORDER BY productid)
2 of 4
1
I think you need to move the AS. WITH testtable AS (..copy code here..) SELECT ..copy code here.. FROM testtable; I could be wrong. But that’s the first thing I spotted.
GeeksforGeeks
geeksforgeeks.org › mysql › how-to-compute-a-running-total-in-mysql
How to Compute a Running Total in MySQL - GeeksforGeeks
July 23, 2025 - 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.
StrataScratch
stratascratch.com › blog › computing-cumulative-sum-in-sql-made-easy
Computing Cumulative Sum in SQL Made Easy - StrataScratch
May 8, 2025 - In SQL, this means accessing all the previous rows, summing them, and adding the sum to the current row’s value. Imagine you’re working with the table showing the daily sales. The cumulative sum for January 1 is 4,578.00, i.e., the same as the sales value for that date.
OneUptime
oneuptime.com › home › blog › how to use running totals with sum() over() in mysql
How to Use Running Totals with SUM() OVER() in MySQL
March 31, 2026 - Compute the cumulative total sales across all categories ordered by date. SELECT sale_date, product_category, amount, SUM(amount) OVER (ORDER BY sale_date, id) AS running_total FROM daily_sales ORDER BY sale_date, id;
Google
discuss.google.dev › looker › q&a › looker
Cumulative Sum in MySQL - Looker - Google Developer forums
May 9, 2015 - Cumulative Sums are a little tricky in MySQL. The simplest way to tackle this would be to use the running_total measure, or utilize the running_total() function in Table Calculations. If you need to be able to referenc…
Medium
medium.com › datadenys › how-to-use-window-functions-in-mysql-541eae9f04eb
How to use window functions in Mysql | by Denys Golotiuk | DataDenys | Medium
November 11, 2022 - So sum(amount) will return total for all rows in a table: ... SELECT id, product_id, amount, avg(amount) over (partition by product_id), min(amount) over (partition by product_id) FROM test LIMIT 10; Which will give us averages and minimums for each window: ... Another popular example is calculating cumulative totals as we go row by row.
Datareportive
datareportive.com › tutorial › mysql › how-to-calculate-cumulative-sum-running-total
How to Calculate Cumulative Sum-Running Total in MySQL | DataReportive Tutorials
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. Before window functions were available in MySQL, one way to calculate a running total was by using session variables.
LabEx
labex.io › tutorials › mysql-how-to-calculate-cumulative-totals-in-mysql-418610
How to calculate cumulative totals in MySQL | LabEx
-- Create a sample sales table ... 75.25); -- Basic cumulative total calculation SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS cumulative_total FROM sales; ... By understanding these basics, database ...
OneUptime
oneuptime.com › home › blog › how to calculate cumulative sums in mysql
How to Calculate Cumulative Sums in MySQL
March 31, 2026 - SELECT month_start, region, revenue, SUM(revenue) OVER ( PARTITION BY region ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_revenue FROM monthly_revenue ORDER BY region, month_start;
Coffingdw
coffingdw.com › mysql-analytics-cumulative-sum
MySQL Analytics – Cumulative Sum – Software connecting all databases
If you took a calculator and added up all of the daily_sales values you see below, you would find that the sum of these values is 442,962.75. The query below sorts the dataset rows by product_id (major sort) and sale_date (minor sort) and then adds up the daily_sales from the first row to the last. I want you to focus on the importance of the ORDER BY statement, which is ORDER BY product_id and then sale_date. Once the data is ordered, the cumulative sum adds up the daily_sales from the first row to the last.
Five
five.co › blog › sql-cumulative-sum
SQL Cumulative Sum: A Practical Guide | Five
May 4, 2026 - Sign up for free access to Five’s online development environment and start building your MySQL web application today. Build Your Database In 3 Steps Start Developing Today ... Cumulative sums are just the beginning. Once you’re comfortable with window functions, you can use similar techniques for other calculations.
GitHub
gist.github.com › MagePsycho › 431233c15e1e0d1532376dcc1b8102f0
MySQL: Running Total (Cumulative Sum) · GitHub
MySQL: Running Total (Cumulative Sum). GitHub Gist: instantly share code, notes, and snippets.
Bipp
bipp.io › sql-tutorial › mysql › calculate-cumulative-total
How to Calculate Cumulative Sum-Running Total | Analysis | MySQL | bipp Analytics
Let’s say we want to see a report with cumulative values, for example the cumulative daily revenue at different timestamps. We want the cumulative revenue at each timestamp in the table: select sales_ts, sum(amount) over (partition by sales_ts order by sales_ts) from sales;
SQLPad
sqlpad.io › tutorial › mastering-cumulative-sums-in-sql-a-comprehensive-guide
Mastering Cumulative Sums in SQL: A Comprehensive Guide
April 29, 2024 - Retail and E-commerce: Businesses track the cumulative sales of products to analyze trends and make inventory decisions. A simple SQL query calculating the cumulative sum of sales day-over-day gives invaluable insights into product performance.
Stack Exchange
dba.stackexchange.com › questions › 328693 › creating-a-cumulative-totals-column
mysql - Creating a cumulative totals column - Database Administrators Stack Exchange
SELECT MONTHNAME(LAST_DAY(CollectionDate)) AS Month, SUM(SaleTotal) AS TotalSalePrice, SUM(CostTotal) AS TotalPurchaseCost, SUM(Profit) AS Profit, (SUM(Profit) / SUM(DriverTotal)) * 100 AS Markup, SUM(SUM(SaleTotal)) OVER (ORDER BY LAST_DAY(CollectionDate) ROWS UNBOUNDED PRECEDING) AS CumulativeSalesPrice SUM(SUM(Profit)) OVER (ORDER BY LAST_DAY(CollectionDate) ROWS UNBOUNDED PRECEDING) AS CumulativeProfit FROM TableA GROUP BY LAST_DAY(CollectionDate) ORDER BY LAST_DAY(CollectionDate); Note the use of a proper grouping construct. Do not rely on MySQL's idiosyncratic method of displaying columsn which are not in the GROUP BY.
DataCamp
datacamp.com › doc › mysql › mysql-sum
MySQL SUM() Function: Usage & Examples
In this syntax, `SUM(column_name)` sums up the values of `column_name` from the specified table or subset of rows.
MySQL
dev.mysql.com › doc › refman › 9.7 › en › aggregate-functions.html
MySQL :: MySQL 9.7 Reference Manual :: 14.19.1 Aggregate Function Descriptions
For numeric arguments, the variance and standard deviation functions return a DOUBLE value. The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE).
W3Resource
w3resource.com › mysql-exercises › mysql-query-to-calculate-the-cumulative-sum-of-sales-for-each-quarter-using-a-window-function.php
Compute Cumulative Sales Per Quarter Using MySQL
SUM(Sales) OVER (ORDER BY Quarter): Calculates the cumulative sum of sales ordered by quarter.
