SQL provides the following aggregate functions that are available for use with a select item that is a field or an expression involving a field:

Using the SQL aggregate functions, you can determine various statistics on sets of values. You can use these functions as a columnItem in a query when creating a Recordset object.

Using SQL Aggregate Functions Run Sample | View Source

The argument colExpr represents a string expression identifying the field that contains the numeric data you want to average, count, evaluate, or sum, or an expression that performs a calculation using the data in that field.

Operands in colExpr can include the name of a table field, a constant, or a function ( which can be either intrinsic or user-defined but not one of the other SQL aggregate functions ).

Now let's explore that in detail.

Avg ( colExpr ) calculates the arithmetic mean of a set of values contained in a specified field on a query.

SELECT ProductType, AVG( UnitPrice ) AS [Average Unit Price] 
   FROM Products GROUP BY ProductType

The average calculated by Avg is the the sum of the values divided by the number of values ( arithmetic mean ). The Avg function does not include any Null fields in the calculation.

Count ( colExpr ) calculates the number of records returned by a query.

You can use Count to count the number of records in the underlying query, or count groups of records.

SELECT Order_Details.ProductID, ProductCode, 
   ProductName, COUNT( * ) AS [Total Records] 
   FROM Order_Details INNER JOIN Products 
   ON Order_Details.ProductID=Products.ProductID 
   GROUP BY Order_Details.ProductID, ProductCode, ProductName 
   ORDER BY COUNT( * ) DESC

Although colExpr can perform a calculation on a field, Count simply tallies the number of records. It does not matter what values are stored in the records.

The Count function does not count records that have Null fields unless colExpr is the asterisk ( * ) wildcard character. If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. Count( * ) is considerably faster than Count( columnName ). Do not enclose the asterisk in quotation marks ( ' ' ).

If colExpr identifies multiple fields, the Count function counts a record only if at least one of the fields is not Null. If all of the specified fields are Null, the record is not counted. Separate the field names with an ampersand ( & ).

The following example shows how you can limit the count to records in which either ShippedDate or Freight is not Null:

SELECT Count( 'ShippedDate & Freight' ) AS [Not Null] FROM Orders;

Min ( colExpr ), Max ( colExpr ) return the minimum or maximum of a set of values contained in a specified field on a query.

You can use Min and Max to determine the smallest and largest values in a field based on the specified aggregation, or grouping. For example, you could use these functions to return the lowest and highest unit price.

SELECT ProductType, MIN( UnitPrice ) AS [Minimun Price of Items], 
   MAX( UnitPrice ) AS [Maximum Price of Items] 
   FROM Products GROUP BY ProductType

If there is no aggregation specified, then the entire table is used.

Using SUM( ), GROUP BY and ORDER BY Run Sample | View Source

The Sum function totals the values of a given columnItem. Sum ( colExpr ) returns the sum of a set of values contained in a specified field or an expression involving a field.

The aggregate functions sample above shows use of Sum( ) to determine the total orders for each ProductID. The example below shows how you can sum the total orders for each group of OrderID's.

SELECT Order_Details.ProductID, ProductCode, ProductName, 
   SUM( Quantity ) AS [Total Orders],
   SUM( Quantity*Order_Details.UnitPrice ) 
      AS [Total Sales in US Dollars] 
   FROM Order_Details INNER JOIN Products 
   ON Order_Details.ProductID=Products.ProductID 
   GROUP BY Order_Details.ProductID, ProductCode, ProductName 
   ORDER BY SUM( Quantity*Order_Details.UnitPrice ) DESC

The Sum function ignores records that contain Null fields.

Note that you cannot nest aggregate functions.