Product of n Rows

oracleoracle-11g-r2

From this data (assuming the number of rows is not known in advance):

with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4)
select * from q1;
/*
C1
--
 2 
 1 
 4 
 1
*/

I want the product of the c1 column from all the rows. Something like the results of SUM(c1) only I want each value multiplied by they others rather than added. In this case that would be 2 * 1 * 4 * 1 = 8. 

/*
X1
--
 8
*/

Data could contain negative numbers and zero, which can be simulated using:

with q1 as (select mod(ora_hash(level),5)-1 c1 from dual connect by level <=4)
select * from q1;

or 

with q1 as (select mod(ora_hash(level),5)-3 c1 from dual connect by level <=4)
select * from q1;

I know this could be done with custom aggregate function, but am interested in native approaches.

Best Answer

For sufficiently small aggregate products, you can use the old trick of summing the logarithms and them exponentiating the result

SQL> ed
Wrote file afiedt.buf

  1  with q1
  2    as (select mod(ora_hash(level),5) c1
  3          from dual
  4       connect by level <=4)
  5  select exp(sum(ln(c1)))
  6*   from q1
SQL> /

EXP(SUM(LN(C1)))
----------------
               8

Since you're using 11.2, it's a bit more verbose (though someone may be able to figure out a simpler version) but you can also use recursive common table expressions

SQL> ed
Wrote file afiedt.buf

  1  with
  2  q1 as (select level l, mod(ora_hash(level),5) c1
  3           from dual
  4        connect by level <= 4),
  5  num(n, c1, running_product)
  6  as
  7  (
  8    select 1 as N,
  9           null as c1,
 10           1 as running_product
 11      from dual
 12    union all
 13    select N+1,
 14           q1.c1,
 15           (q1.c1)*running_product
 16      from num
 17           join q1 on (num.N = q1.l)
 18  )
 19  select running_product
 20    from (select num.*,
 21                 rank() over (order by N desc) rnk
 22            from num)
 23*  where rnk = 1
SQL> /

RUNNING_PRODUCT
---------------
              8
Related Question