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

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

Answers


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

edit:

if col always > 0


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

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

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.


In c# you might have to do:

SELECT EXP(SUM(LOG([col]))) 
  FROM table;

Need Your Help

Assert a function/method was not called using Mock

python unit-testing mocking python-mock

I'm using the Mock library to test my application, but I want to assert that some function was not called. Mock docs talk about methods like mock.assert_called_with and mock.assert_called_once_with...

Setting PATH environment variable in OSX permanently

macos bash unix path environment-variables

I have read several answers on how to set environmental variables on OSX as permanently.