I have this sample table:
Product | Category | QTY | Load Date
----------- --------------- ------------- --------------
1 Alpha 10 Jan. 1, 2021
2 Beta 20 Jan. 1, 2021
3 Charlie 20 Jan. 1, 2021
1 Alpha 30 Jan. 2, 2021
3 Charlie 30 Jan. 3, 2021
1 Alpha -50 Jan. 4, 2021
2 Beta -20 Jan. 4, 2021
4 Delta 40 Jan. 4, 2021
I want to add a column that will sum the quantity base on Previous Load Date, and group by Product and Category.
How can I come up with an output as shown below?
Product | Category | QTY | Load Date | Sum_QTY
----------- --------------- ------------- --------------- ----------
1 Alpha 10 Jan. 1, 2021 10
2 Beta 20 Jan. 1, 2021 20
3 Charlie 20 Jan. 1, 2021 20
1 Alpha 30 Jan. 2, 2021 40
3 Charlie 30 Jan. 3, 2021 50
1 Alpha -50 Jan. 4, 2021 -10
2 Beta -20 Jan. 4, 2021 0
4 Delta 40 Jan. 4, 2021 40
Your help means a lot.
Thank you
CodePudding user response：
If I'm reading the question correctly, using SUM as an analytic function will make the query fairly straight forward;
SELECT *,
SUM("QTY") OVER (PARTITION BY "Product", "Category" ORDER BY "Load Date") Sum_QTY
FROM Table1
ORDER BY "Load Date", "Product", "Category"
CodePudding user response：
Another option is using an outer apply
select t.*,
s.sum_QTY
from Table1 t
outer apply ( select sum(t2.QTY) sum_QTY from Table1 t2 where t2.Category = t.Category and t2.[Load Date] <= t.[Load Date] ) s
ORDER BY [Load Date], Product, Category