SQLITE view, how to calculate diff between buy and sell into same row-CodePudding

I am quit new at this (sqlite) using sqlite studio. I have a table with many rows, trying to view dates, objects, purchase price, sold price and the difference.

This is what I get

DATE    OBJ     BOUGHT  SOLD    DIFF
2009    OBJ1    235000  NULL    NULL
2012    OBJ2    255500  NULL    NULL
2021    OBJ3    305400  NULL    NULL
2012    OBJ1    NULL    255500  NULL
2021    OBJ2    NULL    309000  NULL

This is what I want

DATE    OBJ     BOUGHT  SOLD    DIFF
2009    OBJ1    235000  255500  20500
2012    OBJ2    255500  309000  53500
2021    OBJ3    305400  NULL    NULL

Code:

SELECT strftime('%Y', Op_Date) Year, Op_Obj OBJ ifnull(Op_TTBuy, 0) BOUGHT, ifnull(Op_TTSell, 0) SOLD, (Op_TTSell - Op_TTBuy) DIFF
FROM Operations

I tried many ways.. Any help would be appreciated..

CodePudding user response：

You may need to group by year, something along these lines:

SELECT strftime('%Y', Op_Date) Year, max(Op_Ob) OBJ
, sum(ifnull(Op_TTBuy, 0)) BOUGHT
, sum(ifnull(Op_TTSell, 0)) SOLD
, (sum(Op_TTSell) - sum(Op_TTBuy)) DIFF
FROM Operations 
group by strftime('%Y', Op_Date)
order by strftime('%Y', Op_Date)