SUMIFS Function in SQL - SUM() OVER (PARTITION BY ) is not working

Asked Dec 15 '22 at 11:06

Active Dec 16 '22 at 11:52

Viewed 96 times

I used the SUMIFS formula in my google sheet to sum the product cost based on Order Id and Composite Parent SKU if it is equal to SKU. However, I cannot get the result I want using SUM OVER PARTITION BY in SQL.

Here is my data:

Order_Id	SKU	Composite_Parent_SKU	Quantity	Unit_Cost	Product_Cost
00001	SKU001		1	20	5
00001	SKU001X2		1	30
00001	SKU001	SKU001X2	2		10

What I get if using SUM OVER PARTITION BY:


CASE WHEN Unit_Cost = 0 THEN 0
WHEN Unit_Cost > 0 AND Product_Cost > 0 THEN SUM(Product_Cost * Quantity)
ELSE SUM(Product_Cost * Quantity) OVER (PARTITION BY Order_Id, SKU) END

Order_Id	SKU	Composite_Parent_SKU	Quantity	Unit_Cost	Product_Cost	My_Result
00001	SKU001		1	20	5	15
00001	SKU001X2		1	30
00001	SKU001	SKU001X2	2		10	0

Here is the result that I want:

Order_Id	SKU	Composite_Parent_SKU	Quantity	Unit_Cost	Product_Cost	My_Result
00001	SKU001		1	20	5	5
00001	SKU001X2		1	30		10
00001	SKU001	SKU001X2	2		10

Could anyone please help with this?

Thanks in advance.

edited Dec 16 '22 at 11:52

asked Dec 15 '22 at 11:06

Irene

1

this looks more like a recursive cte solution is need of the cpmüosue parent sku go deeper than one level – nbk Dec 15 '22 at 11:18
@nbk Do you mean use WITH UNION ALL? – Irene Dec 16 '22 at 06:50
yes you could call it that but we prefer the name recursive cte, so that we all know what is meant – nbk Dec 16 '22 at 13:23

SUMIFS Function in SQL - SUM() OVER (PARTITION BY ) is not working

0 Answers0