15,347,383 members
Articles / Database Development
Tip/Trick
Posted 17 Dec 2010

48.9K views
10 bookmarked

Aggregate Product function extends SQL

Rate me:
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):

SQL
`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):

SQL
```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

 Software Developer (Senior) United States
Dr. Alexander Bell is a seasoned full-stack Software Engineer (Win/Web/Mobile). He holds PhD in Electrical and Computer Engineering, authored 37 inventions and published 300+ technical articles. Currently focused on multiple Android/Mobile development projects and Big Data' Machine Learning, AI, IoT. Alex participated in App Innovation Contests (AIC 2102/2013) with multiple winning submissions. Sample portfolio apps and publications:

 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: 26-Jun-22 5:30 Refresh 1