Summing and Analyzing

Microsoft Jet supports the standard SQL aggregate functions Count, Sum, Avg, Max, and Min to perform table-level or group-level totaling:

SELECT 
	Count(Products.UnitPrice) AS CountOfUnitPrice, 
	Sum(Products.UnitPrice) AS SumOfUnitPrice, 
	Avg(Products.UnitPrice) AS AvgOfUnitPrice, 
	Min(Products.UnitPrice) AS MinOfUnitPrice, 
	Max(Products.UnitPrice) AS MaxOfUnitPrice
FROM Products;

The previous query retrieves a single record that collects statistics on all products in the table. It can be combined with a WHERE clause:

SELECT 
	Count(Products.UnitPrice) AS CountOfUnitPrice, 
	Avg(Products.UnitPrice) AS AvgOfUnitPrice
FROM Products
WHERE Products.SupplierID = 6;

If no records match the selection criteria, the output is still produced. The value of any Count aggregate fields is zero; the value of the other aggregate types is Null.

You use the SQL function Count(fieldname) to tally occurrences of non-Null values in a specified field. A special form of Count is Count(*), which counts the number of records. As discussed in the section “Common Pitfalls” later in this chapter, Count(*) should be used whenever possible, because this expression can sometimes be executed more quickly by using the Rushmore query-optimization technology.

See Also For information about how to optimize queries used with Open Database Connectivity (ODBC) data sources, see Chapter 8, “Accessing External Data,” and Chapter 9, “Developing Client/Server Applications.”

The argument to these aggregate functions can be an expression as well as a field name, as in the following example:

SELECT Max(UnitPrice*Quantity) AS MaxExtPrice
FROM [Order Details];

In addition to these standard aggregate functions, Microsoft Jet supports the SQL aggregate functions First and Last, which retrieve the first and last values of the set of records, and the StDev, StDevP, Var, and VarP statistical and financial functions. Note that because the StDev, StDevP, Var, and VarP functions are Microsoft Jet-specific, they aren’t likely to be supported on any other client/server DBMS, and so will have to be evaluated locally.