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 plan SQL:

SELECT POWER(10.0, SUM(LOG10(colname))) AS ProductValue FROM MyTable

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 MyTable

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 floatSET @prod=1SELECT @[email protected] * colname FROM MyTable WHERE colname IS NOT NULLSELECT @prod