The SQL language includes the SUM() aggregate function, that evaluates the sum of all the values in a given column, but it doesn't include a PROD() function that evaluates the product of those values. While this function is surely less commonly useful than SUM(), there are cases when you'd like to use it. Here's how to do it in SQL:
SELECT POWER(10.0, SUM(LOG10(colname))) AS ProductValue FROM TableName
Note that POWER returns the same data type as its argument, so you must pass 10.0 to get a floating point value (if you pass 10 you get an integer result).
Here's another way to get to the same result, but uses natural instead of decimal logarithms:
SELECT EXP(SUM(LOG(colname))) AS ProductValue FROM TableName
There are a few things to keep in mind when adopting this technique. First, all values in colname must be strictly positive, otherwise their logarithm is undefined. Second, all NULL values are automatically ignored. If the column contains negative values you can must use a different approach:
DECLARE @prod float
SET @prod=1
SELECT @prod=@prod * colname FROM TableName WHERE colname IS NOT NULL
SELECT @prod
|
No responses found. Be the first to respond and make money from revenue sharing program.
|