INF: How to Calculate the Product of a Field

 Last reviewed: April 28, 1997
Article ID: Q89656

The information in this article applies to: 

  - Microsoft SQL Server version 4.2 for OS/2

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 : kbtshoot

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.