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.

Bookmark the permalink.

Comments are closed.