# Aggregate Function Product()

By Paul McPherson, 2012/02/06

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: 8344 | Views in the last 30 days: 6

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

## Join the most active online SQL Server Community

### SQL knowledge, delivered daily, free:

#### You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platformâ€¦ And itâ€™s our huge, buzzing community of SQL Server Professionals that makes it such a success.