SQLite How to calculate a difference between two variables of the same table

Question

I am pretty new to SQL and I can't figure out how to sum the amounts (in_amount - out_amount) per asset in the pantry considering the following table.

Ideally I would get this result table (calculation between parentheses for clarification) Thanks for your help!

Answer 1

This looks like union all to unpivot, then aggregation:

select asset, sum(amount) total_amount
from (
    select in_asset as asset, in_amount as amount from mytable
    union all
    select out_asset, - out_amount from mytable
) t
group by asset