SQL Sum with conditions in two columns-CodePudding

I'm very new to SQL and VB.NET. I have an existing table called STOCK with the columns shown here, and I want to sum buy and sell to display current quantity.

Existing table:

ID	Date	BUY	SELL
1	01/01/22	88	0
2	03/01/22	22	0
94669	05/02/22	0	30

I want to display in Current quantity like this

(the current quantity amount in the row above   BUY - SELL)

I add result in Current quantity manually, but I want to do this in automatic way it is possible in SQL code

ID	Date	BUY	SELL	Current quantity
1	01/01/22	88	0	88
2	03/01/22	22	0	110
3	05/02/22	0	30	80

CodePudding user response：

You can try this:

select a.*,
     sum(net_sell) over (order by Curr_date ) as Current_quantity 
from 
(select s.*, 
        buy-sell as net_sell 
from stock s) a ;

Dbfiddle link : https://dbfiddle.uk/?rdbms=postgres_11&fiddle=196a41a578d1e699ccaa3e878e261019

CodePudding user response：

Using @DB08 answer. I do not have the reputation to comment hence posting this.

answer to: how can i Filter by ID example to get result only from rows that have ID 1.

SELECT a.*,
       SUM(net_sell)
         over (
           ORDER BY curr_date ) AS Current_quantity
FROM   (SELECT s.*,
               buy - sell AS net_sell
        FROM   stock s) a
WHERE  a.id = 1