Click here to Skip to main content
13,252,362 members (51,404 online)
Click here to Skip to main content
Add your own
alternative version


10 bookmarked
Posted 17 Dec 2010

Aggregate Product function extends SQL

, 19 Feb 2015
Rate this:
Please Sign up or sign in to vote.
Technique to extend capability of standard SQL by adding the Aggregate Product Function


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

SQL Aggregate Product function based on this Math transfom

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:

P(Xi)=X1*X2*...XN .................................................(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.


1. Aggregate Product function extends SQL


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

  1. Real-time NY Bus Tracking Web App (IoT)
  2. Integrated Sensors Hub (IMU) Testing Web Page
  3. Semaphon™ semantic phone num-to-text converter
  4. Educational Web Portal
  5. Free Online NY Payroll Tax Calculator
  6. WebTV powered by YouTube Player powered by .NET API (#1 on Google)
  7. Top-50 Digital Cameras (by iMark-DCAM rating engine)
  8. Engineering Calculator VOLTA
  9. Big Data: Facebook and YouTube
  10. NY City Trivia Quiz
  11. NY Photo Gallery
  12. Publications by A. Bell

You may also be interested in...

Comments and Discussions

Questiongeneralization to the level of a semigroup? Pin
Vadim Stadnik20-Feb-15 1:08
memberVadim Stadnik20-Feb-15 1:08 
AnswerRe: generalization to the level of a semigroup? Pin
DrABELL21-Feb-15 11:36
professionalDrABELL21-Feb-15 11:36 
GeneralMy vote of 5 Pin
Scott Burkow20-Feb-13 8:31
memberScott Burkow20-Feb-13 8:31 
GeneralRe: My vote of 5 Pin
DrABELL20-Feb-13 19:46
memberDrABELL20-Feb-13 19:46 
GeneralRe: My vote of 5 Pin
Scott Burkow21-Feb-13 9:21
memberScott Burkow21-Feb-13 9:21 
GeneralRe: My vote of 5 Pin
DrABELL21-Feb-13 12:39
memberDrABELL21-Feb-13 12:39 
GeneralRe: My vote of 5 Pin
Scott Burkow22-Feb-13 6:56
memberScott Burkow22-Feb-13 6:56 
GeneralRe: My vote of 5 Pin
DrABELL22-Feb-13 8:44
memberDrABELL22-Feb-13 8:44 
GeneralRe: My vote of 5 Pin
Scott Burkow22-Feb-13 9:40
memberScott Burkow22-Feb-13 9:40 
GeneralReason for my vote of 5 Excellent example of using a little ... Pin
MacMaverick23-Mar-11 2:51
memberMacMaverick23-Mar-11 2:51 
GeneralRe: Many thanks! Kind regards/wishes, Alex Pin
DrABELL23-Mar-11 4:57
memberDrABELL23-Mar-11 4:57 
GeneralReason for my vote of 5 Simple & unusual Pin
A.J.Wegierski14-Mar-11 20:52
memberA.J.Wegierski14-Mar-11 20:52 
GeneralRe: Many thanks! Pin
DrABELL15-Mar-11 4:33
memberDrABELL15-Mar-11 4:33 
GeneralShort URL to the article: Pin
DrABELL4-Jan-11 10:37
memberDrABELL4-Jan-11 10:37 

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

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

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