Subtraction of two sums grouped by asset type?

I have a table, like this:

|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?

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;