Oracle SQL - aggregate multplication

Question

I have the data in the following way:

ITEM     PERCENTAGE
MOBILE | 2
MOBILE | 5
TABLET | 5
TABLET | 3

I want to apply the percentages (eg 2 and 5 for item: MOBILE) as

(1-PERCENTAGE_1%) * (1-PERCENTAGE_2%) --- order does not matter

the final result as

ITEM     PERCENTAGE
MOBILE | 0.931
TABLET | 0.9215

Answer 1

You may use ln and exp functions to convert multiplication into addition and vice versa. But it adds some rounding error that may be significant if number of multipliers is large or their values are too sparse.

select
  item,
  round(exp(sum(ln(1 - PERCENTAGE/100))), 5) as mult
from sample
group by item

Or use model clause to perform row-by-row iteration without approximation that arise when ln/exp are used.

with calc as (
  select *
  from sample
  model
    partition by (item)
    dimension by (row_number() over(partition by item order by null) as dim)
    measures (PERCENTAGE as mult, 0 as last_row)
    rules update (
      mult[any] order by dim = coalesce(mult[cv() - 1], 1)*(1 - mult[cv()]/100),
      /*Last row indicator that will contain aggregated value*/
      last_row[any] = decode(last_row[cv()+1], null, 1, 0)
    )
)
select
  item,
  mult
from calc
where last_row = 1

Or define local PL/SQL function that will iterate over collection of multipliers and perform multiplication. Then apply it to the result of collect aggregate function. Oracle has sys.ODCINumberList predefined type for table of number.

with function f_mult(nums sys.odcinumberlist)
  return number
as
  pragma udf;
  mult number := 1;
begin
  for i in 1..nums.count() loop
    mult := mult * (1 - nums(i)/100);
  end loop;
  return mult;
end;

select
  item,
  f_mult(cast(collect(PERCENTAGE) as sys.odcinumberlist)) as agg
from sample
group by item

For your sample data:

create table sample(ITEM, PERCENTAGE) as
select 'MOBILE', 2 from dual union all
select 'MOBILE', 5 from dual union all
select 'TABLET', 5 from dual union all
select 'TABLET', 3 from dual

all the above queries return the same result:

ITEM	AGG
MOBILE	.931
TABLET	.9215

fiddle