2

I have following table for my little financial web app made with php mysql. I need help with calculation of debit and credit balance in mysql.

1.Table: Entries

+----+---------+------------+------------+
| ID | TYPE_ID | NARRATION  |    DATE    |
+----+---------+------------+------------+
|  1 |       3 | Test Input | 2015-01-05 |
|  2 |       2 | Test Input | 2015-02-07 |
|  3 |       2 | Test Input | 2015-04-02 |
+----+---------+------------+------------+

2.Table: Entry_items

+----+----------+-----------+-------+--------+
| ID | ENTRY_ID | LEDGER_ID | TYPE  | AMOUNT |
+----+----------+-----------+-------+--------+
|  1 |        1 |         1 | D     |   2000 |
|  3 |        1 |         2 | C     |   2000 |
|  4 |        2 |         2 | D     |    550 |
|  5 |        2 |         1 | C     |    550 |
+----+----------+-----------+-------+--------+

So i was able to list amounts with ledger ID=1

SELECT e.date AS date,
 i.type AS type,
 i.amount AS amount
FROM entries AS e LEFT JOIN entry_items AS i ON e.id=i.entry_id
WHERE i.ledger_id = '1'

So result is following

+------------+---+------+
| 2015-01-05 | D | 2000 |
| 2015-02-07 | C |  550 |
+------------+---+------+

So far i could not find the way to calculate the Balance.

The result i want is:

+------------+------+---------+---------+
|    Date    | Type | Amount  | Balance |
+------------+------+---------+---------+
| 2015-01-05 | D    |    2000 |    2000 |
| 2015-02-07 | C    |     550 |    1450 |
+------------+------+---------+---------+

I'm totally lost with my current code:

SELECT e.date AS date,
 i.type AS type,
 i.amount AS amount,
 SUM(COALESCE(CASE WHEN type = 'D' THEN amount END,0)) 
 - SUM(COALESCE(CASE WHEN type = 'C' THEN amount END,0)) balance
FROM entries AS e LEFT JOIN entry_items AS i ON e.id=i.entry_id
WHERE i.ledger_id = '1'
  • 1
    Have you tried to use this method? http://stackoverflow.com/questions/13196190/mysql-subtracting-value-from-previous-row-group-by – Oyeme Jul 08 '15 at 08:16

0 Answers0