CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jony
Jony

Reputation: 23

Sum previous row values in same table

I try to sum value in pervious rows, I want to sum the pervious quantity "Stock on hand" and put the result in 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 summing 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

Upvotes: 1

Views: 186

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

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

Upvotes: 1

Related Questions