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?