Microsoft KB Archive/89656

= How to calculate the product of a field =

Article ID: 89656

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q89656



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)) FROM

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)) FROM

SELECT POWER($10.0, SUM(LOG10)) FROM

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: kbinfo kbother KB89656

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.