-1

I'm having trouble on adding a overall incrementing row to a relatively big SELECT request. The table looks like:

+-----------+------------+
|   Date    |   Amount   |
+-----------+------------+
| 10:11:15  |     25     |
| 10:11:25  |     150    |
| 10:11:35  |     120    |
| 10:11:45  |      90    |
| 10:11:55  |     100    |
+-----------+------------+

And I want to add a new column with the overall amount to that time, like:

+-----------+------------+------------+
|   Date    |  Amount    |  Overall   |
+-----------+------------+------------+
| 10:11:15  |     25     |     25     |
| 10:11:25  |     150    |    175     |
| 10:11:35  |     120    |    295     |
| 10:11:45  |      90    |    385     |
| 10:11:55  |     100    |    485     |
+-----------+------------+------------+

the table is sorted for time, so the last row would show the overall amount till that timestamp. Couldn't find any solution yet, and would be happy if you can help out, so i don't have to hardcode that.

aioobe
  • 413,195
  • 112
  • 811
  • 826
Harderer
  • 51
  • 1
  • 7
  • possible duplicate of [Create a Cumulative Sum Column in MySQL](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) – Sadikhasan Feb 10 '15 at 12:54

2 Answers2

0

Use a sub-select to sum item + all older items.

select Date, amount, (select sum(amount) from yourtable
                      where date <= t1.date) as Overall
from yourtable as t1

Or do a join with group by:

select t1.Date, t1.amount, sum(t2.amount)
from yourtable as t1 join yourtable as t2 on t2.date <= t1.date
group by t1.date, t1.amount
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Thought of that already, but that would be not so easy to implement in my case, i got the date splitted in hour, minute and second. I hoped there is an easier approach, because it seems not that difficult. But thanks for answering. – Harderer Feb 10 '15 at 12:57
  • @Harderer, I have added another way to calculate overall. – jarlh Feb 10 '15 at 13:01
  • Hm yes, I guess I'll have no other option, than doing the time comparison with the three values, for a sql syntax solution. Thanks for the effort. – Harderer Feb 10 '15 at 13:13
  • Found a way to do it with sql syntax, without using the time comparison if you're interested: http://stackoverflow.com/questions/5711192/more-complex-cumulative-sum-column-in-mysql – Harderer Feb 10 '15 at 13:26
0

two place to do it:

1, data engine:

you can code a function like GetOverall_ForThisROw() just do sum() and where PK or some ID < something

2,programming language:

you may put this in your php code, i think this could save the DB.

In my mind, take the original data from DB, then run a easy code in your php will give you best performance.

Benny Ae
  • 1,897
  • 7
  • 25
  • 37
  • Okay, yes I thought I would maybe have to add it to the php code. But it would be so much easier in the sql syntax, because the request is much more complicated, as well as my php code, that requested it. The task seems so simple and I can't figure out, why there wouldn't be a sql syntax solution for it. Thats too bad^^ – Harderer Feb 10 '15 at 13:04