Using the GROUP BY clause for aggregate functions

[ajetrumpet]

Using GROUP BY with aggregate queries

Hi everyone,

I thought I would let you all know of a few things I like to remember when using the GROUP BY clause in queries that involve aggregate data...

1) Even though the GROUP BY clause is written after the selected fields in the query, Access performs the GROUPING first, in order to retrieve the basis on which to perform the aggregate expressions.

2) Fields that are selected, but are not part of any aggregate functions or expressions, must be included in the GROUP BY clause. Note: This requirement will restrict the data you receive based on the number of fields GROUPED, so it is important to select only fields that AFFECT the aggregate expressions.

As a supplement to #2, below are two examples of how the GROUP BY clause affects the output of data....

Say we want to view the total amount of revenue on all items that we sell; we would write...

SELECT [item], sum([total price]) as [Total Revenue]
FROM [orders]
GROUP BY [item];

Now, say we want to view the total amount of revenue (per item) we received from one customer; we would write...

SELECT [item], [customer], sum([total price]) as [Total Revenue]
FROM [orders]
GROUP BY [item], [customer];

It is important to remember that Expressions (e.g. [field]+[field]) and Functions (e.g. Sum([field]), Count(*)) are never grouped. They are simply actions to be performed on the GROUPED fields themselves.

I have attached a transaction log that I worked with (I turned it into a database to make things easier). It contains simple queries (and one fairly complex query) to illustrate the advantages of using the GROUP BY clause with aggregate data.

The "Groupings" example is for reference if anyone may need it.