using mysql calculate balance of each currency where credit and debit in same column? 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.
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.

SELECT CLIENT_ID,
       SUM(IF(currency='USD',IF(ACTION_TYPE='credit',ACTION_AMOUNT, - ACTION_AMOUNT))) as USD_balance,
       SUM(IF(currency='EURO',IF(ACTION_TYPE='credit',ACTION_AMOUNT, - ACTION_AMOUNT))) as EURO_balance,
       SUM(IF(currency='DR',IF(ACTION_TYPE='credit',ACTION_AMOUNT, - ACTION_AMOUNT))) as DR_balance
FROM my_table
GROUP BY CLIENT_ID,currency