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:

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:

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?

## Comments

## Vijaya Kadiyala said:

This is very interesting...

## Madhivanan said:

Also refer

http://beyondrelational.com/blogs/madhivanan/archive/2007/11/13/simple-multiply-function.aspx

## 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

## Mike C said:

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

## Rob Farley said:

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