Oracle sql - aggregated discounts-CodePudding

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.

CodePudding user response：

Add non-Extra to Extra

select t.Stretch, t.FromUnit, t.Discount   ne.s
from mytable t
cross join (
   select sum(Discount) s
   from mytable
   where DiscType != 'Extra'
) ne
where t.DiscType = 'Extra'

CodePudding user response：

You can use:

SELECT Stretch,
       SUM(FromUnit) AS FromUnit,
       SUM(Discount)   MAX(extra_discount) AS discount
FROM   (
  SELECT t.*,
         SUM(CASE WHEN DiscType IN ('Default', 'Bonus') THEN Discount END)
           OVER () AS extra_discount
  FROM   table_name t
)
WHERE DiscType NOT IN ('Default', 'Bonus')
GROUP BY Stretch

Which, for the sample data:

CREATE TABLE table_name ( Stretch, FromUnit, DiscType, Discount ) AS
SELECT 1,  0, 'Default',  5 FROM DUAL UNION ALL
SELECT 1,  0, 'Extra',   15 FROM DUAL UNION ALL
SELECT 2,  7, 'Extra',   17 FROM DUAL UNION ALL
SELECT 3, 10, 'Extra',   20 FROM DUAL UNION ALL
SELECT 1,  0, 'Bonus',    5 FROM DUAL;

Outputs:

STRETCH FROMUNIT DISCOUNT

2 7 27

3 10 30

1 0 25

STRETCH	FROMUNIT	DISCOUNT
2	7	27
3	10	30
1	0	25

db<>fiddle here