# [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;
```

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;
```

`*`

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

More Related questions

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.