Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 8357 | Views in the last 30 days: 2
 
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"

"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    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

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.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones