I have a query which returns transaction history for a particular user.
SELECT amount, type, date FROM wallet WHERE user_id = 1 ORDER BY date DESC
amount type date
100.00 debit 2017-05-18 18:46:32
10.00 debit 2017-05-18 17:51:16
50.00 debit 2017-05-18 17:44:58
50.00 debit 2017-05-18 17:44:08
50.00 credit 2017-05-18 17:42:58
100.00 credit 2017-05-18 17:42:46
100.00 credit 2017-05-18 17:42:23
I want to add a new column balance to the result which shows the balance amount at after that particular transaction. So the above result should look like:
amount type date balance
100.00 debit 2017-05-18 18:46:32 40.00
10.00 debit 2017-05-18 17:51:16 140.00
50.00 debit 2017-05-18 17:44:58 150.00
50.00 debit 2017-05-18 17:44:08 200.00
50.00 credit 2017-05-18 17:42:58 250.00
100.00 credit 2017-05-18 17:42:46 200.00
100.00 credit 2017-05-18 17:42:23 100.00
I can achieve this in PHP. But I am looking for a query which can generate the same result.
You can do something like this if you allow user variables. I don't know if you want to add it beforehand or afterwards.
Note that this query is not tested
SET @balance = (SELECT basebalance FROM wallet WHERE user_id=1);
SELECT amount,
type,
date,
@balance as currentbalance,
@balance:=IF(type='debit', @balance + amount, @balance - amount) as newbalance
FROM wallet
WHERE user_id = 1
ORDER BY date DESC
If you don't allow user variables you have to join the table where w2.ID < w1.currentid. The IFNULL is for the first record which does not have a previous record. Therefor you don't want to get a NULL as response but the first amount.
Also not tested
SELECT w1.amount,
w1.type,
w1.date,
w1.amount + IFNULL(SUM(IF(type='credit', w2.amount * -1, w2.amount)), 0) as balance
FROM wallet w1
LEFT JOIN wallet w2 ON w1.user_id=w2.user_id AND w2.id < w1.id
WHERE w1.user_id = 1
GROUP BY w1.id
ORDER BY w1.date DESC