Like most individuals I find the built int SUM function of SQL very helpful when performing computations. Recently however I found myself wondering how to compute the product of a result set grouped by some factor, and I found myself slightly at a lost as no Product function exists.

Fortunately for us we can use some basic Math to do our aggregate multiplication. As you may or may not remember we can Log and AntiLog to perform multiplication.

X * Y * Z = ANTILOG( LOG( X ) + LOG( Y ) + LOG( Z ) )

Looking closely at the above formula you can see that these functions all exist in most Database Servers and in T-SQL specifically you can rewrite the formula as :

SELECT EXP ( SUM ( LOG (myColumn) ) ) FROM myTable

If you wanted to get even fancier and get totals based on some criteria then you could add in GROUP BY and WHERE clauses to your queries. For example lets say you are doing some probability calculations and want to have the product of some percentages then you could do something like:

 
SELECT EXP(SUM(LOG(r.MEAN)))
FROM RESOURCES r
WHERE r.MODEL_ID = "TEMP"
GROUP BY r.GROUP_ID
ORDER BY r.GROUP_ID

Also it is worth wild to mention this only works for positive values. If you want to have the product of values that could potentially be negative you will have to keep track of the number of negative values and then set the result as necessary.