MySql - 从开始到每天计数

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.

Answer 1

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