The TSQL aggregate function SUM() gives a number based on the addition the values of multiple rows to each other. We wanted to do the same thing but with multiplication instead of addition. A PRODUCT() aggregate function if you will. Something like:

CREATE TABLE #tmp (RowID INT, RowValue INT); INSERT #tmp( RowID, RowValue ) VALUES ( 1, 4); INSERT #tmp( RowID, RowValue ) VALUES ( 1, 2); INSERT #tmp( RowID, RowValue ) VALUES ( 2, 3); INSERT #tmp( RowID, RowValue ) VALUES ( 2, 1); INSERT #tmp( RowID, RowValue ) VALUES ( 3, 2); SELECT * FROM #tmp;

Returns:

`RowID RowValue`

`----------- -----------`

`1 4`

`1 2`

`2 3`

`2 1`

`3 2`

We want to group by RowID to get:

`RowID RowValue`

`----------- -----------`

`1 8`

`2 3`

`3 2`

We found a website that reminded us that LOG_{10}(X) + LOG_{10}(Y) = Z and 10^{Z} = X * Y

In T-SQL it looks like this:

SELECT RowID, POWER(10.,SUM(LOG10(RowValue))) FROM #tmp t GROUP BY RowID

So we still use a SUM() to do a PRODUCT(). Ironic! Anybody remember sliderules?

Notice the decimal point after the first argument of the power function. You need it to force a precession of 18 so that 4 * 2 equal 8 instead of 7.