Skip to Main Content

Help with aggregated discounts

User_UBC84Oct 19 2021 — edited Oct 19 2021

Hi all,
need help with following query to get an aggregated discount.
I have a item and some discounts to be applied depending on the bought quantity.
Stretch / FromUnit / DiscType / Discount
1 / 0 / Default / 5
1 / 0 / Extra / 15
2 / 7 / Extra / 17
3 / 10 / Extra / 20
1 / 0 / Bonus / 5

I need a query to get the following result:
Stretch / FromUnit / Discount
1 / 0 / 25
2 / 7 / 27
3 / 10 / 30

As you can see the default discount (5) applies to all quantities, same for bonus discount, and I need to combine it with the other discounts depending on the stretch they are asigned.
Find some approach using 'sum over partition' like the following but I'm not able to find the correct one...
select stretch, fromUnit, sum(discount) over (partition by stretch) discount
from (select t.*
,row_number() over (partition by stretch, fromUnit, DiscType
order by rownum) as rn
from table t);

Hope I explained myself and thanks in advance.
Alex

This post has been answered by Frank Kulash on Oct 20 2021
Jump to Answer
Comments
Post Details
Added on Oct 19 2021
12 comments
106 views