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

Score: 27
``````select exp(sum(ln(col)))
from table;
``````

edit:

if col always > 0

0

Score: 3
``````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
``````
Score: 3

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
``````
Score: 2

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;
``````
Score: 1

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.

Score: 0

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.

Score: 0

In c# you might have to do:

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

0

More Related questions