SQL Clone
SQLServerCentral is supported by Redgate
 
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"

"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