How to Calculate Cumulative Sum/Running Total in MySQL

Let’s say for our MySQL DVD rental database, we want to see a hockey stick graph of our cumulative rentals by day. First, we’ll need a table with a day column and a count column:

SELECT
  date(rental_date) as day,
  count(rental_id) as rental_count
FROM rental
GROUP BY day;

day        | rental_count
-----------+--------------
2005-05-24 | 8
2005-05-25 | 137
2005-05-26 | 174
2005-05-27 | 166
2005-05-28 | 196

Then we use this to do our cumulative totals. 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

For MySQL 8 you can use a windowed SUM() and also a MySQL common table expression (CTE) instead of a subquery to make it more readable, the result is the same:

with data as (
  select
    date(rental_date) as day,
    count(rental_id) as rental_count
  from rental
  group by day
)

select
  day,
  rental_count,
  sum(rental_count) over (order by day) as cumulative_sum
from data;