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'