Sum previous row values in same table-CodePudding

I try to sum value in pervious rows, I want to sum the pervious quantity "Stock on hand" and put the result in the each row, example

ItemID	Qty	Stockon Hand ( the result updated in this column)
1000	1	1
1000	5	6 ( sum qty previous in pervious row plus the qty in the current row)
1000	2	8 ( sum qty previous in pervious row plus the qty in the current row)
1000	1	9 ( sum qty previous in pervious row plus the qty in the current row)

How can I update the column " Stock on hand" by sum the qty in the current row and previous rows?

select ItemID, Qty
    , sum(qty) over (order by itemid rows between 1 preceding and 1 preceding) as previous_Qty
from #Stock
order by itemid

CodePudding user response：

Here is a working example. Note, I took the liberty of adding an column for a proper sequence... ID, but you could use a date column as well

You may also notice that I added partition by ItemID

Declare @YourTable Table ([ID] int,[ItemID] int,[Qty] int)
Insert Into @YourTable Values 
 (1,1000,1)
,(2,1000,5)
,(3,1000,2)
,(4,1000,1)
 
Select * 
      ,OnHand = sum(Qty) over (partition by ItemID order by ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from @YourTable

Results

ID  ItemID  Qty OnHand
1   1000    1   1
2   1000    5   6
3   1000    2   8
4   1000    1   9

CodePudding user response：

It's called cumulative sum, and the easiest way is to inner join with the same table. It will match all rows with less or equal ID, And will group them into one row with the sum.

In your case:

SELECT a.itemid,a.qty,sum(b.qty)
FROM #stock a INNER JOIN #stock b
ON a.itemid >= b.itemid
GROUP BY a.itenid,a.qty

Further reading: https://www.apdaga.com/2019/02/calculate-cumulative-sum-using-sql-hive-query.html?m=1