devxlogo

Calculate the product of a field

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

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist