Multiplication Aggregate Function in Microsoft SQL

Microsoft SQL has many useful aggregate functions such as AVG, SUM, COUNT — but it doesn’t have a an aggregate function for multiplication. This type of aggregate function would be very useful for needs such as calculating a geometric average.

With a little math, it is possible to calculate a multiplication aggregate result. The following line of SQL is an example of an application of this technique for a field that contains percentages:

SELECT (EXP(SUM(LOG(COALESCE(1 + ([FIELD NAME]/100),1)))) – 1) * 100

The approach is not as efficient as a built-in function, but it may offer superior performance to alternatives.

Linear Regression With Dates

Most of the math libraries I use don’t support date values for linear regression. This was my work around.

1. Convert date / times to Unix time.

2. Take the earliest date and subtract that time value from all the other values.
3. Run linear regression using the modified Unix times.
4. Add the earliest time value back to each Unix time when stating results.