# Product of n Rows

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.

-
You can do this using `SUM(LOG(...))` and a `CASE` expression to handle `0` and `-ve` numbers. –  Martin Smith Mar 14 '12 at 18:44
@Martin That was fast, care to make it a fleshed out answer? –  Leigh Riffel Mar 14 '12 at 18:46
Looks like Justin beat me to it. A SQL Server version here –  Martin Smith Mar 14 '12 at 18:47

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
``````
-
This is what I found as well from viralpatel.net/blogs/2010/11/…. –  Leigh Riffel Mar 14 '12 at 18:49
I'll hold off on accepting this because I am very interested to see if there is another way. –  Leigh Riffel Mar 14 '12 at 18:49
@LeighRiffel - Figured out how to do it with recursive CTE's as well. You could almost certainly do it with the `MODEL` clause too... I may be able to figure that one out later today. –  Justin Cave Mar 14 '12 at 19:13

Justin inspired me to attempt a version using the `MODEL` clause. This is my first use of the clause, so I'm open to any constructive criticism. I created three CTEs to test negative numbers and zero.

``````with q1 as (select level N, mod(ora_hash(level),5) c1 from dual connect by level <=4),
q2 as (select N, c1-1 c1 from q1),
q3 as (select N, c1-3 c1 from q1)
select c1 from q1
model return updated rows dimension by (N) measures (c1)
rules iterate (999) until (c1[iteration_number+1] IS NULL) (
c1[1] = c1[1] * NVL(c1[iteration_number+2],1)
)
union all
select c1 from q2
model return updated rows dimension by (N) measures (c1)
rules iterate (999) until (c1[iteration_number+1] IS NULL) (
c1[1] = c1[1] * NVL(c1[iteration_number+2],1)
)
union all
select c1 from q3
model return updated rows dimension by (N) measures (c1)
rules iterate (999) until (c1[iteration_number+1] IS NULL) (
c1[1] = c1[1] * NVL(c1[iteration_number+2],1)
);
``````

Here is a fleshed out version of the log solution that handles negative and zero values.

``````with q1 as (select mod(ora_hash(level),5)  c1 from dual connect by level <=4),
q2 as (select c1-1 c1 from q1),
q3 as (select c1-3 c1 from q1)
select 'q1',  case when sum(case when c1 < 0 then -1
when c1 > 0 then 1 else 0 end) >= 0 then 1 else -1 end *
decode(min(abs(c1)),0,0,Round(exp(sum(ln(abs(nullif(c1,0))))))) x1
from q1
union all
select 'q2', case when sum(case when c1 < 0 then -1
when c1 > 0 then 1 else 0 end) >= 0 then 1 else -1 end *
decode(min(abs(c1)),0,0,Round(exp(sum(ln(abs(nullif(c1,0))))))) x1
from q2
union all
select 'q3', case when sum(case when c1 < 0 then -1
when c1 > 0 then 1 else 0 end) >= 0 then 1 else -1 end *
decode(min(abs(c1)),0,0,Round(exp(sum(ln(abs(nullif(c1,0))))))) x1
from q3
;
``````

Finally, here is a recursive CTE similar to Justins, but slightly simpler.

``````with q1 as (select level N, mod(ora_hash(level),5) c1 from dual connect by level <=4),
q2 as (select N, c1-1 c1 from q1),
q3 as (select N, c1-3 c1 from q1),
x1 (N, Product) as (
select 1 N, 1 Product from dual
union all
select x1.N+1 N, q1.c1 * x1.Product Product from x1
join q1 on (x1.N = q1.N)
),
x2 (N, Product) as (
select 1 N, 1 Product from dual
union all
select x2.N+1 N, q2.c1 * x2.Product Product from x2
join q2 on (x2.N = q2.N)
),
x3 (N, Product) as (
select 1 N, 1 Product from dual
union all
select x3.N+1 N, q3.c1 * x3.Product Product from x3
join q3 on (x3.N = q3.N)
)
select Product from (select N, Product, max(N) OVER () MaxN from x1) where N = MaxN
union all
select Product from (select N, Product, max(N) OVER () MaxN from x2) where N = MaxN
union all
select Product from (select N, Product, max(N) OVER () MaxN from x3) where N = MaxN;
``````
-