 # 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

## Rate

3.74 (38)

You rated this post out of 5. Change rating