how to subtract in mysql at the same row based on another column
mysql, sql
Solution
You just can have a sign conditioned on your transaction type:
SUM(currency_Amount * IF(Tran_type = "Bought", 1, -1))
Problem
I am trying to subtract sold dollars against bought dollars. ``` **|Tran_ID|Currency_type|currency_Amount|Local_Amount| Rate |Tran_type| ** | 85 | USD | 2500 | 1581.54 |1.58074 | Bought | | 78 | USD | 3487.57 | 2206.29 |1.58074 | Bought | | 86 | USD | 100 | 63.26 |1.58074 | Bought | | 87 | USD | 94.17 | 63.26 |1.48866 | Sold | | 88 | USD | 2600 | 1746.54 |1.48866 | Sold | | 89 | USD | 2600 | 1746.54 |1.48866 | Sold | ``` I can Add the total values of dollars sold or bought with the query below ``` SELECT Currency_type, sum( currency_Amount ) AS total_bought FROM Transaction WHERE Currency_type= 'USD' AND Tran_type = 'Bought' <== **or 'Sold'** ``` the outcome is below ``` **|Currency_type | total_bought |** |USD |6087.570068359375| ```