If all values are positive and there are no nulls...

How about this?

SELECT round(POWER(10.0, SUM(LOG(10,num))), 2) AS product FROM test

-----Original Message-----
From: oracledba-ezmlmshield-x65590764.[Email address protected]
[mailto:oracledba-ezmlmshield-x65590764.[Email address protected]
Sent: Friday, February 08, 2008 1:57 PM
To: [Email address protected]
Subject: Re: Another Friday Question
Importance: Low

SELECT CAST(ROUND(
COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
* SIGN(MIN(ABS(value)))
* (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
,0) AS INTEGER) AS product
FROM TEST
/

Fundamentally it just exploits the fact that the logarithm of the
product is equal to the sum of the logs:
LOG(a) + LOG(b) = LOG(a * b). The SIGN(MIN()) and COUNT(*) expressions
are there to handle negative values and zeros correctly.

LS

"640KB ought to be enough for anybody." - Bill Gates, 1981


----- Original Message ----
From: Chelur Jayadas <oracledba-ezmlmshield-x79983015.[Email address
protected]
To: LazyDBA Discussion <[Email address protected]
Sent: Friday, February 8, 2008 12:16:01 PM
Subject: Another Friday Question

Hi Ed,

Here is a Friday question for you. This time it is in SQL (I am just a
developer, so pardon me for the blasphemy!).

Consider a table TEST having a single column called num which is of
dataype
NUMBER. Assuming that the table has 5 rows, and all have positive
integers
in the num column. There are no NULLs.

If one has to find the total of the num column for all the rows of the
table, it is
quite simple. Use the aggregate function SUM().

But, if one has to find the product of the num column, how would you do
it ?
(It has to be done in a SQL query, and NOT a PL/SQL script or procedure)

Regards,
Jayadas

Here is the script to create the test environment :-
/* DROP TABLE TEST; */

CREATE TABLE TEST
(
num NUMBER NOT NULL
);

INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (2);
INSERT INTO TEST VALUES (3);
INSERT INTO TEST VALUES (4);
INSERT INTO TEST VALUES (5);

COMMIT;



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




Oracle LazyDBA home page