Skip to content

Instantly share code, notes, and snippets.

@MagePsycho
Created August 7, 2017 19:18
Show Gist options
  • Save MagePsycho/431233c15e1e0d1532376dcc1b8102f0 to your computer and use it in GitHub Desktop.
Save MagePsycho/431233c15e1e0d1532376dcc1b8102f0 to your computer and use it in GitHub Desktop.
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
Copy link

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

@NasirJamal786
Copy link

in sqlserver plz

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