Michael J. Swart

So it’s T-SQL Tuesday time again and this month it’s hosted by Jes Schultz Borland.
The topic this month is aggregation. Which is a great topic. Real T-SQL topics are my favorite kind. In the past I’ve actually written a couple of posts on the topic of aggregation which would have fit in perfectly this month:

• CHECKSUM_AGG, a very nifty function
• A Trick for Aggregating Data Fast

But this month, I want to tell you about the aggregate function PRODUCT().

The PRODUCT() Aggregate Function

Okay, I’m pulling your leg a bit. There is no function defined in T-SQL that is called PRODUCT(). But everything is in place to let you build one without having to resort to CLR aggregate functions. All we need is to do is remember a bit of math. Remember that:

So when ever you would want to write

SELECT PRODUCT(field)
FROM SomeTable

You can feel free to write

SELECT EXP(SUM(LOG(field)))
FROM SomeTable

Examples

Multiplying the first six primes

SELECT EXP(SUM(LOG(num))) as product
FROM (VALUES (2),(3),(5),(7),(11),(13)) as NUMS(num)
/*
product
----------------------
30030
*/

The Value of a 2005 U.S. Dollar in 2010

DECLARE @StartYear INT = 2005;
DECLARE @EndYear INT = 2010;
 
SELECT EXP(SUM(LOG(1+ (inflationRate/100.0))))
	AS ValueOfUSDollarAfterInflation
FROM (VALUES
	(2001, 2.85),
	(2002, 1.58),
	(2003, 2.28),
	(2004, 2.66),
	(2005, 3.39),
	(2006, 3.23),
	(2007, 2.85),
	(2008, 3.84),
	(2009, -0.36),
	(2010, 1.64),
	(2011, 0.99)
	) AS RATES([year], inflationRate)
WHERE [year] BETWEEN @StartYear + 1 AND @EndYear
/*
ValueOfADollarAfterInflation
----------------------------
1.11653740799858
*/

More About This Method

I don’t know the first person who came up with this trick (Most likely Napier). With a quick search, I understand that others have written about its implementation in SQL many times before. In fact, I wouldn’t be surprised if this tip comes up again this T-SQL Tuesday. But I post anyway because I like my examples and had fun writing it.