René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

September 22, 2005: On the missing product() aggregate function

Oracle doesn't have a built-in aggregate function that multiplies its arguments (like sum adds expressions).
However, the following mathematical property helps do that with sum:
                   (ln(a) + ln(b) + ... ln(n))
a * b * ... * n = e   
The following tries to show how to exploit this property to emulate the desired product() function.
As usual, when demonstrating an SQL statement, a table is created...
create table t (
  a number,
  b number
);
... and filled with some values:
insert into t values  (   1, 1);
insert into t values  (   1, 1);
insert into t values  (   1, 1);

insert into t values  (   2, 2);
insert into t values  (  -3, 2);
insert into t values  (   4, 2);
insert into t values  (   5, 2);

insert into t values  (   5, 3);
insert into t values  (   0, 3);
insert into t values  (   7, 3);

insert into t values  (   7, 4);
insert into t values  (  -7, 4);

insert into t values  (   5, 5);
insert into t values  (-1/5, 5);
insert into t values  (  -2, 5);
Here's the select statement:
select
  case count(case sign(a) 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(a) 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 a when 0 then null else a end))))
    else 0 -- There were zeroes, so the entire product is 0, too.
  end r, 
  b
from t
group by b
order by b;
Here's the result set. And indeed, 1*1*1=1 (b=1), 2*-3*4*5=-120 (b=2) and so on:
         R          B
---------- ----------
         1          1
      -120          2
         0          3
       -49          4
         2          5

Further links

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.