Calculate oversized total (split by order) in update status
I am trying to generate a total revenue by product subgroups with discounts listed in ascending order.
I used the following query
Update Z_Discount_Tool1 Set Cum_Net_Revenue_Discount =
(Select Sum([Umsatz_Netto]) Over (Partition by [WUGR_act] Order by [Discount] From Z_Discount_Tool1)
However I am getting the following error
The subquery returns more than 1 value. This is not valid when the subquery follows = ,! =, <, <=,>,> = or when a subquery is used as an expression.
I cannot find the error in my code. So any help is appreciated. Thanks in advance!
+3
source to share
1 answer
This is similar to SQL Server. You can use an updatable CTE:
with toupdate as (
select dt.*,
Sum([Umsatz_Netto]) Over (Partition by [WUGR_act] Order by [Discount]) as new_Cum_Net_Revenue_Discount
from Z_Discount_Tool1 dt
)
Update toupdate
Set Cum_Net_Revenue_Discount = new_Cum_Net_Revenue_Discount;
+1
source to share