SQLServerCentral Article

Aggregate Function Product()

,

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 LOG10(X) + LOG10(Y) = Z and 10Z = 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.

Rate

3.74 (38)

You rated this post out of 5. Change rating

Share

Share

Rate

3.74 (38)

You rated this post out of 5. Change rating