Log in  ::  Register  ::  Not logged in

# Aggregate Function Product()

By Paul McPherson,

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.

Total article views: 8418 | Views in the last 30 days: 1

Related Articles
FORUM

### Add RowID Column to Bulk Insert

Add RowID Column to Bulk Insert

FORUM

### Insert returning ROWID needs to STOP!

OLE DB Destination inserts contain "returning rowid into :#var"

FORUM

### RowID in Sql Server

RowID in Sql Server

FORUM

"function"

SCRIPT

### SQL Function to Split Comma Separated Values and Insert into Table

SQL Function to Split Comma Separated Values and Insert into Table

Tags
 aggregates t-sql