12,397,059 members (66,517 online)
Tip/Trick
Add your own
alternative version

23.7K views
10 bookmarked
Posted

# Aggregate Product function extends SQL

, 19 Feb 2015 CPOL
 Rate this:
Please Sign up or sign in to vote.
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.

## License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

## About the Author

 President Infosoft International Inc United States
Dr. A. Bell is a full-stack software developer (Win/Web/Mobile). He holds PhD in EE/IT, published 300+ articles, authored 37 inventions and is credited for 10+ Enterprise level projects; currently focused on HTML5/CSS3, Javascript/jQuery, .NET/WPF/C#, Android/Angular.js, 'Big Data', AI, IoT. Alex participated in App Innovation Contests (AIC 2102/2013) with multiple winning submissions. Sample projects/pubs:

## Comments and Discussions

 First Prev Next
 generalization to the level of a semigroup? Vadim Stadnik20-Feb-15 0:08 Vadim Stadnik 20-Feb-15 0:08
 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: 24-Jul-16 23:07 Refresh 1

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160721.1 | Last Updated 19 Feb 2015
Article Copyright 2010 by DrABELL
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid