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:
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.
No mention of negatives and zeroes? (I guess inflation multipliers are always positive, so it’s fair enough)
(1 – 2*(COUNT(case when inflationRate < 0 then 1 end) % 2)) * MIN(case when inflationRate = 0 then 0 else 1 end) * EXP(SUM(LOG(ABS(NULLIF(inflationRate,0)))))
All good though – it's nice to be reminded of this stuff.
Rob
Comment by Rob Farley — March 7, 2011 @ 9:13 pm
Thanks Rob,
I left the mention of zeroes and negatives to keen commenters like yourself.
Cheers
Comment by Michael J Swart — March 7, 2011 @ 10:25 pm
Nice, although we did touch it briefly also in this thread
Lightning Fast Hybrid RUNNING TOTAL – Can you slow it down?
Comment by Naomi — March 8, 2011 @ 9:22 pm
Hi Naomi,
Thanks for the link. It’s interesting that it got mentioned by my friend Brad Shulz in that thread.
In my post I linked to a google search above (and fixed the link just now). I was pointing to the search http://www.google.com/search?q=%2B%22EXP+SUM+LOG%22+%2Bsql which shows thousands of people who have discovered this before I did. C’est la vie.
But none of them feature a picture of the Slap Chop guy do they 😉
Comment by Michael J. Swart — March 8, 2011 @ 9:43 pm
Cool stuff, Michael. Thanks for sharing!
Comment by Jes Schultz Borland — March 8, 2011 @ 9:45 pm
The earliest hit I find via google is:
http://planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=159&lngWId=5
Submitted by Umachandar on 7/25/2000 8:42:10 PM
http://www.umachandar.com/
Comment by Erik Eckhardt — March 17, 2011 @ 7:24 pm
This seems unreliable if you want to work in the domain of ints. See http://stackoverflow.com/questions/31343533/update-with-non-clr-product-aggregate-results-in-off-by-one. The problem is that LOG and EXP work with floats and so we get the “usual” issues of floating point rounding.
Comment by Damien — July 10, 2015 @ 10:54 am
That’s actually pretty surprising Damien. Thanks for the link.
I was never super-proud of this article… I may update it or take it down altogether.
Comment by Michael J. Swart — July 10, 2015 @ 11:23 am
Throwing away a method because it occasionally gives you a result which is out by a tiny fraction is not a reason to throw out the method, but to take more care about casting as an integer.
I’m sure with not too much trouble you could find an example of multiplying an integer by a float, and getting a result which is X-0.0000000000001 instead of just X, such that if you cast that as an integer it will round down. When dealing with floats and ints it is important to ROUND instead of CAST.
This method of PRODUCT is just fine. It’s the handling of data types that has caused the stackoverflow question.
Comment by Rob Farley — July 10, 2015 @ 6:12 pm
You’re right Rob, but this PRODUCT trick is just not super-important (to me personally). I’ve never used it or needed to use it except as an exercise in aggregates.
I’d be interested to hear about real-world examples which make use of this PRODUCT method.
Comment by Michael J. Swart — July 20, 2015 @ 9:10 am
I agree that PRODUCT() is a rare thing, but there are legitimate uses for it in heavier maths, such as when dealing with vector multiplication using matrices… Probably not necessarily something you’d do in SQL, but that depends on your sources and other stuff.
Comment by Rob Farley — July 20, 2015 @ 9:21 am
Right. That’s my experience, that there’s rarely an overlap between heavy math and things one does in SQL.
Comment by Michael J. Swart — July 20, 2015 @ 11:31 am
really great
Comment by karthika — November 23, 2016 @ 6:34 am
I had a reason for this. I have a Product Structure Table where I wanted to roll up the quantities of several parent child entries.
You need to multiply the quantity times the parent quantity times the grandparent quantity times the great grand parent quantity….etc.
I was able to get all the parents at all levels with one query. Then, I have local quantity of each row. Now, condense them at get the product. This will break with zeros however.
Comment by Kent Keller — January 16, 2017 @ 5:43 pm
I have a need for something like this; I work as an analyst at a call center, and I’m trying to multiply probabilities…
Comment by George — January 25, 2019 @ 8:48 am
Thank you! This makes my work much easier. This lets me run some of my smaller data science projects inside of SQL instead of pushing the data to R or Python.
Comment by Bill Capps — January 22, 2020 @ 2:34 pm
Good post
If anyone want to reads about SQL server aggregate functions here is the link
aggregate function in sql server
Comment by Vikram — August 1, 2020 @ 5:53 am
I am trying to convert/aggregate my daily investment returns into one monthly return.
Day 1 = 2%
Day 2 = -0.7%
Day 3 = -1.20%
Day 4= -1.05%
How would I achieve this in SQL? A Python Pandas solution is also welcome
Thanks!!
Comment by Andrew — August 19, 2021 @ 7:19 pm