Skip to content
Advertisement

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

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. Table

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

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement