Home » RDBMS Server » Server Administration » sql
sql [message #375219] Mon, 06 August 2001 16:20 Go to next message
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 Go to previous messageGo to next message
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
Re: sql [message #375233 is a reply to message #375222] Tue, 07 August 2001 16:44 Go to previous message
madhuri
Messages: 12
Registered: August 2001
Junior Member
Thanks Hans.
Previous Topic: installation of oracle 8.1.7
Next Topic: OEM
Goto Forum:
  


Current Time: Tue Dec 24 06:58:14 CST 2024