I have this table named Stock
ID | Plant | Item | Quantity
1 1 Pepsi 1
2 1 Coke 3
3 2 Pepsi 5
How to reconstruct previous state to get this result?
ID | Plant | Item | Quantity | THIS(total_stock)
1 1 Pepsi 1 6
2 1 Coke 3 3
3 2 Pepsi 5 6
That can be done using a window function:
select id, plant, item, quantity,
sum(quantity) over (partition by item) as total_stock
from stock;