Aggregate Query Woes

[NeoPa]

Introduction :

How many times have you come across the following error message when working within Aggregate, or Sum, queries?

Access Error:
You tried to execute a query that does not include the specified expression 'X' as part of an aggregate function.

What is this message relating to and how can one avoid it?

Aggregate (Sum) Functions :

These are the standard functions available to Jet SQL. I can't say no others can exist, nor that they may not be introduced later into Jet SQL itself, but these are currently available :
Sum(), Avg(), Min(), Max(), Count(), StDev(), Var(), First() and Last()

Converting a Query to Aggregate (or Sum) :

In SQL the GROUP BY clause can be used, but is actually not absolutely necessary. Any reference to an aggregate function within the SQL will cause the query to behave as an aggregate one. If no GROUP BY clause is included the whole dataset is treated as a single group.

In Design View select View | Totals, or click on the Sigma-shaped button (Like an 'M' rotated 90 degrees anti-clockwise), to change the options to allow selecting of aggregate functions and also fields to group by.

Access Help :

It may help to start with what Access Help has to say on the subject :

Jet SQL Help:
All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.

This quote from the Help system implies that all references to fields, even within compound references, must either be aggregated (IE. included in one of the aggregate functions listed above) or included in the GROUP BY clause. Any expression which is of either type is considered aggregated. The aggregate functions can only take field reference expressions which resolve to non-aggregated fields (IE. It is equally invalid to aggregate data more than once).

Explanation and Example Illustration :

Essentially, the SQL Engine needs to know how to process any particular reference. If you have a reference to a field which is not inside an aggregate function of any type, and is also not in the GROUP BY clause, then how is the SQL Engine to know which of the potentially many records in the group to take the value from.

See the situation below and ask yourself whether the result for [fB] should be "A", "B" or "C" :

SELECT   [fA]
       , [fB]
       , Sum([fC]) AS [SumC]
FROM     [Tbl]
GROUP BY [fA]

Table = [Tbl]
 [fA]  [fB]  [fC]
 1    "A"    11
 1    "B"    22
 1    "C"    33

Result :

[fA]=1, [fB]="???", [SumC]=66

Conclusion :

The fact that you cannot determine from this information which item should be selected, is an illustration of why it doesn't make sense unless the above criteria are met (In short that all field references are aggregated in one way or another).