How to calculate running total (balance) per group

Question

I have a table like this in mysql where only three field id,credit and debit exists now i want to calculate the current balance as like below in examp column i show how i want mysql to calculate it will search for just previous time current balance. table is ordered by time.....

id  credit  debit   Time                balance  Examp
1   100             1/6/2013 11:11      100 
1           50      1/6/2013 13:14      50      (100-50)
1   20              2/6/2013 10:10      70      (50+20)
2   200             1/6/2013 11:12      200
2   100             1/6/2013 11:14      300     (200 + 100)
2           150     2/6/2013 10:10      150     (300 -150)

something similar is answered here http://stackoverflow.com/questions/9394418/calculate-balance-with-mysql ... for your specific task, you would have to somehow group the running balance by id in the above solution — UserBSS1, Jun 28 '13 at 07:39
I guess.. but I don't see any effort to try writing those SELECTs.. — Ms. Nobody, Jun 28 '13 at 07:50

score 3 · Accepted Answer · answered Jun 28 '13 at 07:48

Try

SELECT id, credit, debit, time, balance
FROM
(
  SELECT t.*, @n := IF(@g <> id, 0, @n) + COALESCE(credit,0) - COALESCE(debit, 0) balance, @g := id
    FROM table1 t, (SELECT @n := 0) n, (SELECT @g := 0) g
   ORDER BY id, time
) q

Output:

+------+--------+-------+---------------------+---------+
| id   | credit | debit | time                | balance |
+------+--------+-------+---------------------+---------+
|    1 |    100 |  NULL | 2013-01-06 11:11:00 |     100 |
|    1 |   NULL |    50 | 2013-01-06 13:14:00 |      50 |
|    1 |     20 |  NULL | 2013-02-06 10:10:00 |      70 |
|    2 |    200 |  NULL | 2013-01-06 11:12:00 |     200 |
|    2 |    100 |  NULL | 2013-01-06 11:14:00 |     300 |
|    2 |   NULL |   150 | 2013-02-06 10:10:00 |     150 |
+------+--------+-------+---------------------+---------+

Here is SQLFiddle demo

I didn't know there was a SQLFiddle too.. thanks for that :) — Ms. Nobody, Jun 28 '13 at 08:08
@user2481959 You're more than welcome. I'm glad I could help :) — peterm, Jun 28 '13 at 16:15

How to calculate running total (balance) per group

1 Answers1