is there a PRODUCT function like there is a SUM function in Oracle SQL?

Question

I have a coworker looking for this, and I don't recall ever running into anything like that.

Is there a reasonable technique that would let you simulate it?

SELECT PRODUCT(X)
FROM
(
    SELECT 3 X FROM DUAL
    UNION ALL 
    SELECT 5 X FROM DUAL
    UNION ALL
    SELECT 2 X FROM DUAL
)

would yield 30

Answer 1

DECLARE @a int
SET @a = 1
-- re-assign @a for each row in the result
-- as what @a was before * the value in the row
SELECT @a = @a * amount
FROM theTable

There's a way to do string concat that is similiar:

DECLARE @b varchar(max)
SET @b = ""

SELECT @b = @b + CustomerName
FROM Customers

Answer 2

There are many different implmentations of "SQL". When you say "does sql have" are you referring to a specific ANSI version of SQL, or a vendor specific implementation. DavidB's answer is one that works in a few different environments I have tested but depending on your environment you could write or find a function exactly like what you are asking for. Say you were using Microsoft SQL Server 2005, then a possible solution would be to write a custom aggregator in .net code named PRODUCT which would allow your original query to work exactly as you have written it.

Answer 3

Here's another way to do it. This is definitely the longer way to do it but it was part of a fun project.

You've got to reach back to school for this one, lol. They key to remember here is that LOG is the inverse of Exponent.

LOG10(X*Y) = LOG10(X) + LOG10(Y)

or

ln(X*Y) = ln(X) + ln(Y) (ln = natural log, or simply Log base 10)

Example
If X=5 and Y=6

X * Y = 30

ln(5) + ln(6) = 3.4

ln(30) = 3.4

e^3.4 = 30, so does 5 x 6

EXP(3.4) = 30

So above, if 5 and 6 each occupied a row in the table, we take the natural log of each value, sum up the rows, then take the exponent of the sum to get 30.

Below is the code in a SQL statement for SQL Server. Some editing is likely required to make it run on Oracle. Hopefully it's not a big difference but I suspect at least the CASE statement isn't the same on Oracle. You'll notice some extra stuff in there to test if the sign of the row is negative.

CREATE TABLE DUAL (VAL INT NOT NULL)
INSERT DUAL VALUES (3)
INSERT DUAL VALUES (5)
INSERT DUAL VALUES (2)

    SELECT 
           CASE SUM(CASE WHEN SIGN(VAL) = -1 THEN 1 ELSE 0 END) % 2 
               WHEN 1 THEN -1 
               ELSE 1 
           END
         * CASE 
                WHEN SUM(VAL) = 0           THEN 0 
                WHEN SUM(VAL) IS NOT NULL   THEN EXP(SUM(LOG(ABS(CASE WHEN SIGN(VAL) <> 0 THEN VAL END)))) 
                ELSE NULL 
           END
         * CASE MIN(ABS(VAL)) WHEN 0 THEN 0 ELSE 1 END
        AS PRODUCT 
      FROM DUAL

Answer 4

It looks like you can program this functionality into Oracle. This technique can support parallelism, unlike the MS-SQL technique in my other answer. Spiffy.

Answer 5

select exp(sum(ln(col))) from table

edit:

if col always > 0