We help IT Professionals succeed at work.

replicating product function from excel to sql

philsivyer
philsivyer used Ask the Experts™
on
Trying to retun values as per logic used in excel re "PRODUCT" function.
Best to see attached excel sheet for logic.

Regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Attached file
product.xls

Author

Commented:
Hello
That works re PRODUCT function but not as a compound Array Product function as in example spreadsheet.

Regards
IT-specialist
Commented:
You want products per key  ?

with my_table as
(
select 'A' cpy, 'B' region, 1 key, to_number(-0.0351909917829243000000000)   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(0.0205308384115177000000000 )   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(-0.0289636250922636000000000)   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(0.0129284072700446000000000 )   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(0.0295839688236403000000000 )   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(0.0048436304562871100000000 )   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(-0.0217224334067565000000000)   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(-0.0132686892676699000000000)   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(-0.0659560108471949000000000)   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(-0.0613088132827362000000000)   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(-0.0205846724588282000000000)   amount from dual union
select 'A' cpy, 'B' region, 1 key, to_number(-0.0046666250560921700000000)   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0266328711573787000000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0018889239833708200000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0084493312434679200000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0157582963697380000000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0354830478177792000000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0065968994563310200000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0189983634618873000000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0155299700296885000000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0239431223558233000000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(-0.0086276663182312000000000)   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0073252364317641900000000 )   amount from dual union
select 'A' cpy, 'B' region, 2 key, to_number(0.0134223137399825000000000 )   amount from dual
)
select cpy,region,key,
  case count(case sign(amount + 1) when 0 then 1 else null end) -- count zeros in group
    when 0 then -- No zeroes: proceed normally
      -- ln only accepts positive values. Here, we count how many negative numbers there were in a group:
      case mod(sum(case sign(amount + 1) when -1 then 1 else 0 end),2)
        when 1 then -1  -- Odd number of negative numbers: result will be negative
        else 1          -- Even number of negative numbers: result will be positive
      end *             -- Multiply -1 or 1 with the following expression
      exp(sum(ln(
        -- only positive (non-zero) values!
        abs(case amount + 1 when 0 then null else amount + 1 end))))
    else 0 -- There were zeroes, so the entire product is 0, too.
  end product
from my_table
group by cpy,region,key
order by cpy,region,key
/

C R        KEY    PRODUCT
- - ---------- ----------
A B          1 -.17333695
A B          2 .177651581

rounding the results at 4 decimals wil get you the same results as in the sheet

Author

Commented:
Hello
This question is different in so far as the previous one was how to return a "PRODUCT" value over a given number of rows.
This one is where I want the exp value returned as per the example in the excel spreadsheet which returns a different value. (its based on an array formual as used in excel)
eg {product(1+L62:L73)-1}
The answer given here is exactly what I want.
Regards