Why is MySQL cumulative sum producing wrong results

Question

I need to find the cumulative sum for the following data:

Following query:

SELECT created, COUNT( * ) 
FROM  `transactions` 
GROUP BY created

Gives me:

created COUNT( * )  
2015-8-09   1
2015-8-15   1
2015-8-16   2
2015-8-17   1
2015-8-23   1

I tried to do the cumulative sum like:

SELECT t1.created, COUNT( * ) , SUM( t2.totalcount ) AS sum
FROM transactions t1
INNER JOIN (

SELECT id, created c, COUNT( * ) AS totalcount
FROM transactions
GROUP BY created
ORDER BY created
)t2 ON t1.id >= t2.id
GROUP BY t1.created
ORDER BY t1.created

but the results it gives arent as expected:

created COUNT( * )  sum 
2015-8-09   5   6
2015-8-15   3   4
2015-8-16   6   8
2015-8-17   1   1
2015-8-23   4   5

How do i produce the following result:

created COUNT( * )  sum 
2015-8-09   1   1
2015-8-15   1   2
2015-8-16   2   4
2015-8-17   1   5
2015-8-23   1   6

Possible duplicate of [Create a Cumulative Sum Column in MySQL](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) — e4c5, Oct 25 '15 at 01:27

juergen d · Accepted Answer · 2015-10-25T02:03:06.317

3

select tmp.*, @sum := @sum + cnt as cum_sum
from
(
  SELECT created, COUNT( * ) as cnt 
  FROM  `transactions` 
  GROUP BY created
  ORDER BY created
) tmp
cross join (select @sum := 0) s

edited Oct 25 '15 at 02:03

answered Oct 25 '15 at 01:51

juergen d

201,996
37
293
362

score 2 · Answer 2 · answered Oct 25 '15 at 01:40

Your inner query is selecting id without grouping on it. Let's rework it in terms of the date.

SELECT t1.created, COUNT( * ) AS daycount, SUM( t2.totalcount ) AS sum
  FROM transactions t1
 INNER JOIN ( SELECT created, COUNT( * ) AS totalcount
                FROM transactions
               GROUP BY created
            ) t2 ON t1.created >= t2.created
 GROUP BY t1.created
 ORDER BY t1.created;

Or you might want to put the totalcount inline:

SELECT t1.created, COUNT(*) AS daycount
     , ( SELECT COUNT(*) FROM transactions t2
          WHERE t2.created <= t1.created ) AS totalcount
  FROM transactions t1
 GROUP BY created
 ORDER BY CREATED;

The other answer is clearly from someone who better understands MySql (I'm an Oracle guy), and will probably be more efficient. Use that one. — Paul Kienitz, Oct 25 '15 at 01:56

Why is MySQL cumulative sum producing wrong results

2 Answers2