sql [message #375219] |
Mon, 06 August 2001 16:20 |
madhuri
Messages: 12 Registered: August 2001
|
Junior Member |
|
|
Hi,
If I have a table with one column in it. the column has the random values.(for eg. 20,3,4,7,10).
How can I get the product of the column in one select statement.'cause oracle dont have the function 'PRODUCT'. I can get the product by writing PL/SQL block but how can I get it in a single select statement. please help.
thank you.
|
|
|
Re: sql [message #375222 is a reply to message #375219] |
Tue, 07 August 2001 07:35 |
Hans
Messages: 42 Registered: September 2000
|
Member |
|
|
this solution is based on the formula
log(a_1) + log(a_2) + .. + log(a_n) = log( a_1*a_2*..*a_n) for a_i > 0 (i=1,..,n)
and thus the product could be expressed via the sum(), log() and the power() function.
Are all of your numbers > 0 ?
drop table prod;
create table prod (
num number(5)
);
insert into prod ( num ) values ( 1 );
insert into prod ( num ) values ( 2 );
insert into prod ( num ) values ( 3 );
insert into prod ( num ) values ( 4 );
insert into prod ( num ) values ( 5 );
insert into prod ( num ) values ( 6 );
commit;
select power( 10, sum( log( 10, num ) ) ) product from prod;
PRODUCT
----------
720
|
|
|
|