Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SS65,SS7
Expertise: Intermediate
Jan 1, 2003



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Calculate the product of a field

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 float
SET @prod=1
SELECT @prod=@prod * colname FROM MyTable WHERE colname IS NOT NULL
SELECT @prod
Francesco Balena
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date