Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL: Running Total (Cumulative Sum)
-- without using SET variable
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
-- with SET variable
SET @running_total := 0;
SELECT
q1.d,
q1.c,
(@running_total := @running_total + q1.c) AS rt
FROM
(SELECT
DAYOFYEAR(`date`) AS d,
COUNT(*) AS c
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d
) AS q1
@supremumk

This comment has been minimized.

Copy link

@supremumk supremumk commented Jun 17, 2018

I am thinking that you may need to use window function when the running total is also in a subgroup.

@NasirJamal786

This comment has been minimized.

Copy link

@NasirJamal786 NasirJamal786 commented Nov 20, 2019

in sqlserver plz

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment