Read 328 times | Created 2014-04-13 15:14:01 | Updated 2014-04-15 04:25:53 | | |

 

DROP TABLE IF EXISTS transactions;
CREATE TABLE transactions(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
debit INT null,
credit INT null
) ENGINE=MyISAM;


INSERT INTO transactions VALUES
(1,10,0),
(2,0,40),
(3,50,0),
(4,0,10),
(5,0,10);

SELECT * FROM transactions;
+----+-------+--------+
| id | debit | credit |
+----+-------+--------+
|  1 |    10 |      0 |
|  2 |     0 |     40 |
|  3 |    50 |      0 |
|  4 |     0 |     10 |
|  5 |     0 |     10 |
+----+-------+--------+

SELECT x.id
     , x.debit
     , x.credit
     , SUM(y.bal) AS balance 
  FROM
     ( 
       SELECT *,debit-credit AS bal 
       FROM transactions
     ) x
  JOIN
     ( 
       SELECT *,debit-credit AS bal 
       FROM transactions
     ) y
    ON y.id <= x.id
 GROUP 
    BY x.id;

+----+-------+--------+---------+
| id | debit | credit | balance |
+----+-------+--------+---------+
|  1 |    10 |      0 |      10 |
|  2 |     0 |     40 |     -30 |
|  3 |    50 |      0 |      20 |
|  4 |     0 |     10 |      10 |
|  5 |     0 |     10 |       0 |
+----+-------+--------+---------+