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

On the collect aggregate function

user defined aggregate functions.

More on Oracle

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


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