Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cumulative sum over days

I have a MySQL table like the following:

date         count
2010-01-01   5
2010-01-02   6
2010-01-03   7

How can I accumulate the sum of each day to the next one? So the result is like:

date         acum per day
2010-01-01   5
2010-01-02   11
2010-01-03   18

I think i need some kind of for(each date)... but no clue.


Just the final query i used following answer from Eric. (thanks).

SELECT t1.dia, sum(t2.operacions), sum(t2.amount) FROM

(SELECT count(*) operations, sum(amount), date(b.timestamp) dia
    FROM transactions b group by date(b.timestamp)) t1 

INNER JOIN 

(SELECT count(*) operations, sum(amount), date(b.timestamp) dia
    FROM transactions b group by date(b.timestamp)) t2 

ON t2.dia <= t1.dia GROUP BY t1.dia
like image 948
user373259 Avatar asked Jan 21 '23 21:01

user373259


2 Answers

Well, I think this would work, not sure how the performance would be though:

SELECT t1.date, sum(t2.count)
FROM mytable t1 INNER JOIN mytable t2 ON t2.date <= t1.date
GROUP BY t1.date
like image 184
Eric Petroelje Avatar answered Jan 31 '23 16:01

Eric Petroelje


It's possible to solve this problem without join.

SET @cumulative_sum := 0;
SELECT date, @cumulative_sum := @cumulative_sum + count AS cumulative_sum
FROM table
ORDER BY date ASC
like image 33
Naktibalda Avatar answered Jan 31 '23 14:01

Naktibalda