CODEWITHSUNDEEP
sqlsql-servercumulative-sum
Charlotte
Charlotte

Reputation: 31

Calculate running total with over(partition by order by) within update statment

I am trying to create a running total of revenues over product subgroups with discounts given ordered ascending.

I have 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 receive the following error message

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I can't find the error in my code. So any help is appreciated. Thanks in advance!

Upvotes: 3

Views: 515

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

This looks like 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;

Upvotes: 1

Related Questions