12,953,402 members (48,594 online)
Tip/Trick
alternative version

#### Stats

27.3K views
10 bookmarked
Posted 17 Dec 2010

# Aggregate Product function extends SQL

, 19 Feb 2015 CPOL
 Rate this:
Technique to extend capability of standard SQL by adding the Aggregate Product Function

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

## Share

 President Infosoft International Inc United States
Dr. Alexander Bell is a seasoned full-stack Software Engineer (Win/Web/Mobile). He holds PhD in EE/IT, authored 37 inventions and published 300+ technical articles. Currently focused on HTML5/CSS3, Javascript, .NET/WPF/C#, Angular.js, SQL, 'Big Data', Machine Learning, AI, IoT. Alex participated in App Innovation Contests (AIC 2102/2013) with multiple winning submissions. Portfolio samples:

## You may also be interested in...

 First Prev Next
 Re: generalization to the level of a semigroup? DrABELL21-Feb-15 10:36 DrABELL 21-Feb-15 10:36
 My vote of 5 Scott Burkow20-Feb-13 7:31 Scott Burkow 20-Feb-13 7:31
 Re: My vote of 5 DrABELL20-Feb-13 18:46 DrABELL 20-Feb-13 18:46
 Re: My vote of 5 Scott Burkow21-Feb-13 8:21 Scott Burkow 21-Feb-13 8:21
 Re: My vote of 5 DrABELL21-Feb-13 11:39 DrABELL 21-Feb-13 11:39
 Re: My vote of 5 Scott Burkow22-Feb-13 5:56 Scott Burkow 22-Feb-13 5:56
 Re: My vote of 5 DrABELL22-Feb-13 7:44 DrABELL 22-Feb-13 7:44
 Re: My vote of 5 Scott Burkow22-Feb-13 8:40 Scott Burkow 22-Feb-13 8:40
 Reason for my vote of 5 Excellent example of using a little ... MacMaverick23-Mar-11 1:51 MacMaverick 23-Mar-11 1:51
 Re: Many thanks! Kind regards/wishes, Alex DrABELL23-Mar-11 3:57 DrABELL 23-Mar-11 3:57
 Reason for my vote of 5 Simple & unusual A.J.Wegierski14-Mar-11 19:52 A.J.Wegierski 14-Mar-11 19:52
 Re: Many thanks! DrABELL15-Mar-11 3:33 DrABELL 15-Mar-11 3:33
 Short URL to the article: http://exm.nr/PrdSQL DrABELL4-Jan-11 9:37 DrABELL 4-Jan-11 9:37
 Last Visit: 31-Dec-99 18:00     Last Update: 27-May-17 14:10 Refresh 1