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

September 22, 2005: On the missing product() aggregate function  
Oracle doesn't have a builtin 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 (nonzero) 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 linksMore on OracleThis is an on Oracle article. The most current articles of this series can be found here.
