shabir ullah shabir ullah - 2 months ago 8
MySQL Question

add multi type currency and than getting total of each type

I have a table

transaction
having field
s_no,amount
,
transaction_type("Debit","Credit")
,symbol.

I want to sum all amount for debit and also sum of amount for transaction_type credit against each symbol i.e $,Rs,AED currency symbol.
And at the end total that
is
debit-credit
.

I tried this one:

Answer

Would a simple GROUP BY solve your issue?

SELECT `symbol`, SUM(IF(transaction_type = 'Debit', `amount` * -1, `amount`)) amount
FROM `transaction`
GROUP BY `symbol`

Obviously you'd want to adjust your indexes to support this, probably on (symbol,amount)