Creating a cumulative sum column in MySQL

Question

Sample table ID: (num is a key so there wouldn't be any duplicates)

Desired output:
(Should be sorted and have a cumulative sum column)

num cumulative
1    1
2    3
3    6
5    11
6    17
8    25

This is one solution I got:

select a.num, sum(b.num) from ID a, ID b where b.num <= a.num group by a.num order by a.num;

AFAIK, MySQL can't really do this sort of thing. This being referencing previous rows. You could use a temporary table, I suppose, but it might be better doing this sort of thing clientside. — Bojangles
– Bojangles, Commented Oct 2, 2011 at 21:12
Just a fun problem i got while playing with mysql. Trying to use joins. — varunl
– varunl, Commented Oct 2, 2011 at 21:15
You could certainly do it with variables, common someone will certainly show the @sum answer. I'm lazy. — regilero
– regilero, Commented Oct 2, 2011 at 21:47

The Scrum Meister · Accepted Answer · 2011-10-03 07:07:32Z

7

You can use a temporary variable to calculate the cumulative sum:

SELECT  a.num,
   (@s := @s + a.num) AS cumulative
FROM ID a, (SELECT @s := 0) dm
ORDER BY a.num;

answered Oct 3, 2011 at 7:07

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The Scrum Meister Over a year ago

Anyone using the new MySql 8.0 should instead use CTE

varunl · Accepted Answer · 2011-10-02 21:38:37Z

3

I think I figured out the solution.

Select num as n, 
       (select sum(num) from ID where num <= n)
from ID order by n;

answered Oct 2, 2011 at 21:38

varunl

20.4k5 gold badges33 silver badges47 bronze badges

2 Comments

Matthew Farwell Over a year ago

Actually, it doesn't work if you have duplicates, i.e. 1,2,3,3,4,5, you'll get 0,1,3,3,9,13, which I don't think is what you want.

varunl Over a year ago

Yup, that totally makes sense. I was looking at only unique values. Will update that in the question.

Lukas Eder · Accepted Answer · 2018-09-17 15:18:03Z

1

Since MySQL 8, cumulative sums are ideally calculated using window functions. In your case, run:

SELECT num, SUM(num) OVER (ORDER BY num) cumulative
FROM id

answered Sep 17, 2018 at 15:18

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

2 Comments

haneulkim Over a year ago

do u know why w/o order by it ouputs all sum not cumulative?

Lukas Eder Over a year ago

@Ambleu: Yes, see here: stackoverflow.com/questions/17664436/…

Community · Accepted Answer · 2017-05-23 12:02:36Z

0

as these answer i already tested in my project and actually i want to know which one is faster so i also posted this here which one is faster

declare @tmp table(ind int identity(1,1),col1 int)
insert into @tmp
select 2
union
select 4
union
select 7
union 

select 5
union
select 8
union 
select 10


 SELECT t1.col1,sum( t2.col1)
    FROM @tmp AS t1 LEFT JOIN @tmp t2 ON t1.ind>=t2.ind
    group by t1.ind,t1.col1

select t1.col1,(select sum(col1) from  @tmp as t2 where t2.ind<=t1.ind)
from @tmp as t1

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Oct 3, 2011 at 7:02

rahularyansharma

10.8k18 gold badges86 silver badges140 bronze badges

Collectives™ on Stack Overflow

Creating a cumulative sum column in MySQL

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related