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.