How to calculate the product of a field (89656)

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.