Click here to Skip to main content
Click here to Skip to main content

Aggregate Product function extends SQL

, 14 Mar 2011
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

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

License

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

Share

About the Author

DrABELL
President Infosoft International Inc
United States 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:
  1. Edumatter M12: School Math Calculators and Equation Solvers (contest winner)
  2. Engineering Calculator VOLTA-2013 (contest winner)
  3. HTML5 Best Practices: Table formatting via CSS3
  4. Edumatter-M12 for Windows, app overview
  5. Engineering Calculator VOLTA-814D
  6. CoolPhone: phone numbers-to-text converter
  7. SQL generates large data sequence
  8. Aggregate Product function extends SQL
  9. Top-50 Digital Cameras
  10. WebTV Project: Embedded YouTube Player (Goog #1 YouTube API for ASP.NET)
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.
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 PinmemberScott Burkow20-Feb-13 7:31 
GeneralRe: My vote of 5 PinmemberDrABELL20-Feb-13 18:46 
GeneralRe: My vote of 5 PinmemberScott Burkow21-Feb-13 8:21 
GeneralRe: My vote of 5 PinmemberDrABELL21-Feb-13 11:39 
GeneralRe: My vote of 5 PinmemberScott Burkow22-Feb-13 5:56 
GeneralRe: My vote of 5 PinmemberDrABELL22-Feb-13 7:44 
GeneralRe: My vote of 5 PinmemberScott Burkow22-Feb-13 8:40 
GeneralReason for my vote of 5 Excellent example of using a little ... PinmemberMacMaverick23-Mar-11 1:51 
GeneralRe: Many thanks! Kind regards/wishes, Alex PinmemberDrABELL23-Mar-11 3:57 
GeneralReason for my vote of 5 Simple & unusual PinmemberA.J.Wegierski14-Mar-11 19:52 
GeneralRe: Many thanks! PinmemberDrABELL15-Mar-11 3:33 
GeneralShort URL to the article: http://exm.nr/PrdSQL PinmemberDrABELL4-Jan-11 9:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

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.140826.1 | Last Updated 14 Mar 2011
Article Copyright 2010 by DrABELL
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid