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

### References

1. **Aggregate Product function extends SQL**