MySql - count from beginning until each day

Question

Let's say I have the following table (keep in mind that this table will have 10000+ rows):

id    total           date
1       5        2015-05-16
2       8        2015-05-17
3       4        2015-05-18
4       9        2015-05-19
5       3        2015-05-20

I want the query to give the following result:

1
date => 2015-05-16
total => 5

2
date => 2015-05-17
total => 13

3
date => 2015-05-18
total => 17

4
date => 2015-05-19
total => 26

5
date => 2015-05-20
total -> 29

I can't think of any query that would do this right now, that's why I am not providing any code that I have tried.

Any thoughts? I am not sure if this is possible only with mysql, maybe I have to use and php.

possible duplicate of [Create a Cumulative Sum Column in MySQL](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) — Tim Biegeleisen, May 20 '15 at 08:26

score 0 · Answer 1 · answered May 20 '15 at 08:29

0

You can do this -

SELECT 
     a.id, 
     a.date, 
    (SELECT SUM(b.total) FROM your_table WHERE b.date <= a.date) as new_total       
FROM your_table a, your_table b 
ORDER BY a.date ASC

answered May 20 '15 at 08:29

Sougata Bose

31,517
8
49
87

score 0 · Accepted Answer · answered May 20 '15 at 08:30

0

This could be done using user defined variable in mysql and then get the running total as

select
id,
total,
date
date from
(
 select
 id,
 @tot:= @tot+total as total,
 date from my_table,(select @tot:=0)x
 order by date
)x

answered May 20 '15 at 08:30

Abhik Chakraborty

44,654
6
52
63

score 0 · Answer 3 · answered May 20 '15 at 08:34

0

This should do it:

select id, (select sum(total) from table a where a.date <= b.date)  from table b

answered May 20 '15 at 08:34

fkurth

868
1
8
11

MySql - count from beginning until each day

3 Answers3