With the following MySQL table containing debit or credit "actions" with associated amounts and currency, how is it possible to select all CLIENT_IDs with a non-zero "balance" of each currency? I have tried but something isn't working correctly.
CLIENT_ID ACTION_TYPE ACTION_AMOUNT Currency
1 debit 1000 USD
1 credit 100 USD
1 credit 500 DR
2 debit 1000 EURO
2 credit 1200 DR
3 debit 1000 EURO
3 credit 1000 USD
4 debit 1000 USD
My MySQL query that doesn't work:
select CLIENT_ID,
sum(if(ACTION_TYPE='credit',ACTION_AMOUNT,0)) as credit,
sum(if(ACTION_TYPE='debit',ACTION_AMOUNT,0)) as debit,
sum(if(currency='USD',ACTION_TYPE='credit'- ACTION_TYPE='debit')) as USD_balance,
sum(if(currency='EURO',ACTION_TYPE='credit'- ACTION_TYPE='debit')) as EURO_balance,
sum(if(currency='DR',ACTION_TYPE='credit'- ACTION_TYPE='debit')) as DR_balance
FROM my_table
GROUP BY CLIENT_ID,currency
The result I am expecting is something like:
CLIENT_ID USD_Balance EURO_Balance DR_Balance
1 -900 0 500
2 0 -1000 1200
3 1000 -1000 0
4 -1000 0 0
I have no idea what else to try. Any help would be great.
Best Answer
I would try something like this to get the results that you are looking for. What this is doing is determining how to add everything up based on the record being a credit or debit and subtracting the debit values instead of adding them.