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
select exp(sum(ln(col))) from table;
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)
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;