# Aggregate Product function extends SQL

By , 14 Mar 2011
 Rate this:

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

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[^]

President Infosoft International Inc
United States
Dr. A. Bell has 20+ years of Software and Electrical Engineering experience. He is Win/Web veteran, published 200+ articles and authored 37 inventions, currently focused on: Windows 7/8, HTML5, CSS3, jQuery, SQL, .NET, ASP.NET, WPF, C#, Speech Technology and Mobile apps. He's been among recent App submission winners (The Windows 8* & Ultrabook™ App Innovation Contest 2012). Sample pubs:
Dr. Bell is personally credited for 10+ Enterprise level projects (Finance/Investment, Engineering, Edu) w/total code base exceeding 250k lines; doing consulting in NYC for 20 yrs.

 First PrevNext
 My vote of 5 Scott Burkow 20-Feb-13 7:31
 Re: My vote of 5 DrABELL 20-Feb-13 18:46
 Re: My vote of 5 Scott Burkow 21-Feb-13 8:21
 Re: My vote of 5 DrABELL 21-Feb-13 11:39
 Re: My vote of 5 Scott Burkow 22-Feb-13 5:56
 Re: My vote of 5 DrABELL 22-Feb-13 7:44
 Re: My vote of 5 Scott Burkow 22-Feb-13 8:40
 Re: Many thanks! Kind regards/wishes, Alex DrABELL 23-Mar-11 3:57
 Reason for my vote of 5 Simple & unusual A.J.Wegierski 14-Mar-11 19:52
 Last Visit: 31-Dec-99 18:00     Last Update: 9-Mar-14 22:16 Refresh 12 Next »

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140309.2 | Last Updated 14 Mar 2011