2

I am pulling some data from MySQL with PHP. I have added a ORDER by ID ASC clause to the query and this gives me results in the desired order.The ID column is the incrementing ID in MySQL to ensure uniqueness. What I want to do is as follows:

  ID       Payment       Accumulated Totals
  1        £200          £200
  2        £325          £525
  5        £205          £730
  7        £100          £830

As I am still a newbie, I am wondering how to implement the Accumulated totals column as shown above. I am thinking there should be some formula to work out accumulated totals in each row. I have failed to work out something though I am battling with this problem right now and will post code once I come up with something whether or not it works just to show my efforts.

Any help or guidance will be greatly appreciated.

Bululu
  • 555
  • 2
  • 8
  • 14
  • I amy not have been clear, and sorry about that. What I send to mysql is only the payment and not the totals. As a payment is made, a user submits it via a form ending up with a table like: – Bululu Oct 22 '13 at 14:57
  • I may not have been clear, and sorry about that. What I send to mysql is only the payment and not the totals. As a payment is made, a user submits it via a form ending up with a table in mysql with columns id payment only. Then the table above is what a user sees when viewing the records. So i hope to get the table above by using PHP after querying the data from MySQL. I ma seeing joints in answers (thanks for all the help) just that i only have one table in mysql. – Bululu Oct 22 '13 at 15:03
  • @Bululu. The Joins you see are done between the same table (see my sqlfiddle examples). What don't you like about the approaches there? – Filipe Silva Oct 22 '13 at 15:04
  • I think it's clear what you're doing. The duplicate question I linked to shows how to calculate running totals, isn't that what you want? – Barmar Oct 22 '13 at 15:17
  • @Filipe Silva, thanks for the explanation. It's not a matter of liking rather it's not knowing. I am not yet into joints so I have always thought joints are only used between 2 tables. I will try these answers and see what works. – Bululu Oct 23 '13 at 10:20
  • @Bululu. You can use JOIN's (without the t.lol) in the same table the same way you would do with a second table. In this case to get the sum of all payments related to the id's smaller than the one in the "first" table. – Filipe Silva Oct 23 '13 at 11:08

2 Answers2

0
select t1.id, t1.Payment, SUM(t2.Payment) as Accumulated_Totals
from table t1
inner join table t2 on t1.id >= t2.id
group by t1.id, t1.Payment
order by t1.id
Teja
  • 13,214
  • 36
  • 93
  • 155
  • Note that this is pretty inefficient if the table is large, because it performs a huge cross-product. – Barmar Oct 22 '13 at 13:46
0

You can check various implementations of cumulative sum in this answer.

About your data, if you have the £ symbols inside your values, the sum will return 0. You will have to do something like : 

REPLACE(Payment, '£', '') for it to be perceived as a summable value.

Here's a sqlfiddle showing how it can work for the two approaches mentioned in the previous answers.

Community
  • 1
  • 1
Filipe Silva
  • 21,189
  • 5
  • 53
  • 68