Preamble
The suggested solution extends the capability of Structured Query Language (SQL) by adding the Aggregate Product function. Entire 'pure SQL' solution is encapsulated into a single query, portable to any SQL backed databases, for example, Microsoft Access or SQL Server.
1. Underlying Math Transforms
Fig.1. SQL Aggregate Product function based on this underlying Math transfom
Standard SQL contains several aggregate functions (Sum, Count, Min, Max, etc.) with noticeable absence of aggregate Product. As a reminder, Product function P of multiple arguments (X1, X2,...XN) is defined as:
N
P(Xi)=X1*X2*...XN .................................................(1)
i=1
Database engine cannot perform the aggregate product calculation directly, but it can calculate sums. Simple mathematical transforms provide a workaround enabling to compute the product P by using the standard built-in mathematical Log(), Exp() and SQL aggregated Sum() functions; the core technique is illustrated by mathematical formulas (2) and (3):
Log(X1*X2*... XN)= Log(X1)+Log(X2)+...Log(XN) ......................(2),
N N
P(Xi)= Exp(SUM(Log(Xi))) ............................................(3)
i=1 i=1
The last formula (3) could be translated into SQL statement in a rather straightforward manner, enabling the calculation of aggregate Product by means of standard built-in SQL functions.
2. Programming Technique: Math-to-SQL Translation
This simple yet practical example will demonstrate the SQL programming technique enabling to calculate the Product of all positive numbers {2, 4, 5, 7, 8} stored in a Microsoft Access Table1. Based on the precondition that there are no any negative values, a simple SQL query can do the job of calculating Product (SQL 1):
SELECT Exp(Sum(Log([Num]))) AS P FROM Table1
The statement could be modified with IIf() conditional operator added in order to handle zeros(SQL 2):
SELECT Exp(Sum(IIf([Num]=0,0,Log([Num]))))*IIf(Min([Num])=0,0,1) AS P
FROM Table1
The solution has been implemented/tested in Microsoft Access 2003/2007; it is also portable to any other SQL-backed Database. For detailed discussion of this SQL technique, please refer to the online article [1], published by the author and included in the reference section.
