INF: How to Calculate the Product of a Field

ID: Q89656

The information in this article applies to:

SUMMARY

This article describes how to determine the product of the values in a field. This method behaves in the same fashion as if there is a PRODUCT() aggregate function.


MORE INFORMATION

To calculate the product for a particular column, you can take the base 10 log of the values, sum them, and then take the antilog (using the POWER() function with a base 10) of the summation.

Example

Assume that you have the following table with a column of number to be multiplied: 

   col
   ---
   5
   14
   2

The product of these values is 5 * 14 * 2 = 140

To calculate this product in SQL, you can use the following query: 

   SELECT POWER(10, SUM(LOG10(<col>)))
   FROM <table>

Note that the POWER() function will return the same data type as that of the numeric expression. The numeric expression is 10, therefore in the above example, an INT will be returned. In order to return a datatype FLOAT or MONEY, the numeric expression must be 10.0 or $10.0, respectively: 

   SELECT POWER(10.0, SUM(LOG10(<col>)))
   FROM <table>

   SELECT POWER($10.0, SUM(LOG10(<col>)))
   FROM <table>

You need to take the same care with all datatypes that are supported by the POWER() function.

NOTE: as with all functions, some rounding errors may occur.

Additional query words: Transact-SQL multiplication multiple 


Keywords          : kbother SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        :

Last Reviewed: March 13, 1999