[ACCEPTED]-is there a PRODUCT function like there is a SUM function in Oracle SQL?-oracle

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

edit:

if col always > 0

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 1 similiar:

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

SELECT @b = @b + CustomerName
FROM Customers

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

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

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

or

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

Example
If 13 X=5 and Y=6

X * Y = 30

ln(5) + ln(6) = 3.4

ln(30) = 3.4

e^3.4 12 = 30, so does 5 x 6

EXP(3.4) = 30

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

Below is the code in 7 a SQL statement for SQL Server. Some editing 6 is likely required to make it run on Oracle. Hopefully 5 it's not a big difference but I suspect 4 at least the CASE statement isn't the same 3 on Oracle. You'll notice some extra stuff 2 in there to test if the sign of the row 1 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

The accepted answer by tuinstoel is correct, of course:

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

But notice that 5 if col is of type NUMBER, you will find tremendous performance 4 improvement when using BINARY_DOUBLE instead. Ideally, you 3 would have a BINARY_DOUBLE column in your table, but 2 if that's not possible, you can still cast 1 col to BINARY_DOUBLE. I got a 100x improvement in a simple test that I documented here, for this cast:

select exp(sum(ln(cast(col as binary_double))))
  from table;

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

One technique could 6 be using LISTAGG to generate product_expression 5 string and XMLTABLE + GETXMLTYPE to evaluate it:

WITH cte AS (
  SELECT grp, LISTAGG(l, '*') AS product_expression
  FROM t
  GROUP BY grp
)
SELECT c.*, s.val AS product_value
FROM cte c
CROSS APPLY(
    SELECT *
    FROM XMLTABLE('/ROWSET/ROW/*' 
         PASSING dbms_xmlgen.getXMLType('SELECT ' || c.product_expression || ' FROM dual')
         COLUMNS val NUMBER PATH '.')
) s;

db<>fiddle demo

Output:

+------+---------------------+---------------+
| GRP  | PRODUCT_EXPRESSION  | PRODUCT_VALUE |
+------+---------------------+---------------+
| b    | 2*6                 |            12 |
| a    | 3*5*7               |           105 |
+------+---------------------+---------------+

More 4 roboust version with handling single NULL 3 value in the group:

WITH cte AS (
  SELECT grp, LISTAGG(l, '*') AS product_expression
  FROM t
  GROUP BY grp
)
SELECT c.*, s.val AS product_value
FROM cte c
OUTER APPLY(
   SELECT *
   FROM XMLTABLE('/ROWSET/ROW/*' 
        passing dbms_xmlgen.getXMLType('SELECT ' || c.product_expression || ' FROM dual')
        COLUMNS val NUMBER PATH '.')
   WHERE c.product_expression IS NOT NULL
) s;

db<>fiddle demo

*CROSS/OUTER APPLY(Oracle 2 12c) is used for convenience and could be 1 replaced with nested subqueries.

This approach could be used for generating different aggregation functions.

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

In c# you might have to do:

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

0