<%dim crumb crumb2="Aggregate Function Query" crumb1="Aggregate Functions Query Examples
Select Min, Select Max, Select Count Example" crumb="Aggregate Function Query" %> Aggregate Functions
Home   Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles   Search   Contact  Privacy  Links
<% response.write crumb2 %>
<% response.write crumb1 %>
Home > Tutorials > Query Tutorials > <% response.write crumb %>

Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
SQL Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Access Append Query
Master Detail Update
Order By Query
SQL Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Access SQL Select Top
SQL Union Query
SQL Update Query
Custom Query Function


MultiSelect Parameter Forms Access Tutorial

Aggregate Function Query

Aggregate function query performs calculations as calculations within an SQL query.  Aggregates are most often used in combination with a Group By clause. Below are the SQL aggregate functions available in an MS Access query:

Select Sum Determine total of the field values
Select Avg Average of the field values
Select Min Lowest (minimum) field value
Select Max Highest (maximum) field value
Select Count Count of the values other than nulls
Select StDev Standard deviation of the field values including date/time fields
Select Var Variance of the field values including date/time

Here is a simple example of the Select Sum Aggregate function query in Access:

SELECT Sum([Rm_Rate]) AS Expr1 FROM M_Trip_Booking;

Here are some examples of aggregate function usage:

Select Count Aggregate Function Query

Select Count(Emp_ID) From M_Employees;

The query above simply counts the autonumber field in the M_Employees table.

Select Average Aggregate Function Example

Select Avg(Emp_Salary) From M_Employees Where Emp_Age<50;

The above aggregate query determines the average salary for employees under 50 years of age.

The SQL aggregate query below gets a little interesting by showing you how to answer more complex question of your data:

ID Emp_Name Emp_Salary Emp_Age
1 Joe $18.00 51
2 billy $17.00 52
3 Molly $16.00 53
4 bobby $15.00 41
5 robert $14.00 42
6 milly $13.00 43
7 harry $12.00 44
8 ed $11.00 45
9 sally $10.00 46

SELECT avg(iif(emp_age>=50,Emp_Salary,null)) as Over_50_Salary,avg(iif(emp_age<50,emp_salary,null)) as Under_50_Salary
FROM M_Employees;

Over_50_Salary Under_50_Salary
$17.00 $12.50

Note the use of the immediate if (iif) to bracket the results and return two columns where the would normally be only one.

Note there is no Group By which is normally associated with an SQL aggregate function query.

Note we are using aliases (as) to generate our own column names.

Note nulls are skipped in the average aggregate and that is what makes this query work.

Select StDev Query

Using the Employee table shown above now we get the standard deviation of the salary column:

SELECT StDev([Emp_Salary]) AS Salary_Standard_Deviation FROM M_Employees;

Results are pretty simple:

SQL Aggregate Standard Deviation Query

SQL Select Var Aggregate Variance Query

SELECT Var([Emp_Salary]) AS Salary_Variance FROM M_Employees;

The results are shown below:

SQL Aggregate Variance Query

Here are some additional techniques to consider...

When creating a report it is often useful to have the Count Average and Standard Deviation listed at the bottom of each column of numbers.  As fairly simple technique is to use a series of union queries to build the result rows.

The first query would retrieve all the raw numbers in a multi row list.

The second union query would append the aggregate count function.

The third union query would add the aggregate average function.  Finally the last union select would add the standard deviation.

Another technique to try out is to use the aggregate functions in the scalar query.

Warning: Most (if not all) sql aggregate functions will cause memo fields to be truncated to 255 characters.

More Aggregate Function Information:

Domain Aggregate Functions in MS Access
SQL Aggregate Function Examples: Dlookup, Dmin, Dmax, Dlast, Dfirst, DAvg, DSum, Dcount, DStdev, DstdevP,Dvar, DvarP...

Advanced Union Query Download
In this example we selecting the individual data records from the table and at the same time using the union query function to select SQL Aggregate functions...

Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365

Contact Information
<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>
Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials