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: 8364 | Views in the last 30 days: 3
 
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"

FORUM

How to get rowid in sql server 2005

I want to get the rowid internally generated at table level in sql server.

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