I have a table, like this:
x
|id| user_id|xact_type|amount|asset_type|
|1 | 1| Credit| 100| usd|
|2 | 2| Debit| 100| usd|
|3 | 1| Credit| 100| eur|
|4 | 2| Debit| 100| eur|
|5 | 1| Debit| 50| eur|
|6 | 2| Credit| 50| eur|
I need to sum all amounts which type is Credit and all amounts which type is Debit for user_id = 1 grouping by asset_type respectively. And then subtract sum of debits from sum of credits for each asset_type. E.g. For example table and user_id = 1 it would be: 100 usd (no debits for this asset type) 50 eur (credit – debit, 100 – 50)
|user_id|sum|asset_type|
| 1|100| usd|
| 1| 50| eur|
How can i do this in a single query?
Advertisement
Answer
You can use conditional aggregation:
select user_id, asset_type,
sum(case when xact_type = 'Credit' then amount
when xact_type = 'Debit' then - amount
else 0
end)
from t
group by user_id, asset_type;