THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Coles: Sergeant SQL

SQL Server development, news and information from the front lines

Almost T-SQL Tuesday #003: Calculating Products - An Oldie But Goodie

Looks like I just missed the deadline for T-SQL Tuesday #003 [oh well, whatcha gonna do].  The other day someone asked me about an oldie but goodie that hits on a math-based relationship. SQL Server has several aggregate functions like SUM for summation. Summation is the addition of a set of numbers, and it's represented with sigma notation, like this:

Sigma notation from Wikipedia

The question was how do you calculate a product in SQL?  Keep in mind that SQL has no built-in PRODUCT aggregate. A product is the multiplication of a series of numbers, and is represented with capital PI notation:

Capital PI notation 

The question was how do you perform a product aggregation in SQL? The most obvious way to solve the problem is with a simple cursor or loop. You could also create a SQL CLR UDF. But we can solve it even simpler than that, in pure SQL. So how do we solve it with a SQL set-based solution?

Well, mathematics provides us a relationship between logarithms and exponents that allows us to turn a product into a simple summation. The code below demonstrates.

CREATE TABLE Tab
(
 
val float
);
GO

INSERT
INTO Tab (val)
VALUES (9.3),
 
(4.5),
 
(2.6),
 
(11.4),
 
(3);
GO

SELECT
EXP(SUM(LOG(val))) AS product
FROM Tab;
GO

The sample sums the natural logarithms of val, and then returns the exponential of that sum. The end result is the product of the values in the val column. One thing to watch out for is that this method will error out if any of the values are <= 0. You can accomodate for this issue with a CASE expression, but that's for another day.

 WHO DAT?

Published Wednesday, February 10, 2010 1:11 AM by Mike C

Comments

 

Vijaya Kadiyala said:

This is very interesting...

February 10, 2010 9:26 AM
 

Madhivanan said:

February 11, 2010 1:05 AM
 

Rob Farley said:

Thanks Michael. If you want to stop errors because of negatives and zero, then use EXP(SUM(LOG(ABS(NULLIF(val, 0))))), and if you want to detect the zero, then consider having something along the lines of * (1 - SIGN(COUNT(CASE WHEN val = 0 THEN 1 END))), and to make sure the sign is correct, try: * (1 - 2 * (COUNT(CASE WHEN val < 0 THEN 1 END) % 2))

To give you a final formula of:

  EXP(SUM(LOG(ABS(NULLIF(val, 0))))) * (1 - SIGN(COUNT(CASE WHEN val = 0 THEN 1 END))) * (1 - 2 * (COUNT(CASE WHEN val < 0 THEN 1 END) % 2))

Great post. :)

Rob

February 12, 2010 9:15 PM
 

Mike C said:

Thanks Rob, I was thinking of using the CASE expression but hadn't considered using the SIGN function.  Great tip!

February 13, 2010 4:30 PM
 

Rob Farley said:

(Reposted from my msmvps.com blog ) Lots of blog posts for this month, for the first T-SQL Tuesday to

February 20, 2010 1:32 AM
New Comments to this post are disabled

This Blog

Syndication

News

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement