Summary and Grouping Behavior in the Query Designer

When you create aggregate queries, certain logical principles apply. For example, you cannot display the contents of individual rows in a summary query. The Query Designer helps you comply with these principles in the way the Diagram and Grid panes behave.

By understanding the principles of aggregate queries and the Query Designer's behavior, you can create logically correct aggregate queries. The overriding principle is that aggregate queries can result only in summary information. Thus, most of the principles that follow describe the ways that you can reference individual data columns within an aggregate query.

• Referencing Columns for Output and Sorting
  
• Referencing Columns in Search Conditions
  
  
• Working with Columns in Aggregate Queries

Referencing Columns for Output and Sorting

The following principles describe how you can reference columns in an aggregate query for output and for sorting:

• If you include an aggregate function anywhere in a query, the query is considered an aggregate query. (This principle does not necessarily apply to subqueries, which can include nonaggregate information.)
  
  
• You cannot display the contents of individual rows in an aggregate query; you can display only summary data. As a consequence, all columns marked for output must also be assigned to either an aggregate function or to the GROUP BY clause.
  
  
• Columns used in aggregate functions cannot appear in the GROUP BY clause.

Referencing Columns in Search Conditions

The following principles describe how you can reference columns in an aggregate query in search conditions. In general, you can use a column in a search condition to filter the rows that should be summarized (a WHERE clause) or to determine which grouped results appear in the final output (a HAVING clause).

• Individual data columns can appear in either the WHERE or HAVING clause, depending on how they are used elsewhere in the query.
  
  
• WHERE clauses are used to select a subset of rows for summarizing and grouping and are thus applied before any grouping is done. Therefore, you can use a data column in a WHERE clause even if it is not part of the GROUP BY clause or contained in an aggregate function. For example, the following statement selects all titles that cost more than $10.00 and averages the price:

  SELECT AVG(price)
  FROM titles
  WHERE price > 10

• If you create a search condition that involves a column also used in a GROUP BY clause or aggregate function, the search condition can appear as either a WHERE clause or a HAVING clause — you can decide which when you create the condition. For example, the following statement creates an average price for the titles for each publisher, then displays the average for the publishers in which the average price is greater than $10.00:

  SELECT pub_id, AVG(price)
  FROM titles
  GROUP BY pub_id
  HAVING (AVG(price) > 10)

• If you use an aggregate function in a search condition, the condition involves a summary and must therefore be part of the HAVING clause.

Working with Columns in Aggregate Queries

When you create aggregate queries using the Diagram and Grid panes, the Query Designer makes certain assumptions so that your query adheres to the principles outlined earlier. For example, if you are creating an aggregate query and mark a data column for output, the Query Designer automatically makes the column part of the GROUP BY clause so that you do not inadvertently attempt to display the contents of an individual row in a summary.

Even though the Query Designer works to help prevent logical errors, it is possible to create aggregate queries that will not execute. Therefore, be sure that you understand the principles listed earlier in order to avoid errors when you create queries.

The Query Designer uses the following guidelines for working with columns:

• When you choose the Group By option or add an aggregate function to a query, all columns marked for output or used for sorting are automatically added to the GROUP BY clause. Columns are not automatically added to the GROUP BY clause if they are already part of an aggregate function.

  If you do not want a particular column to be part of the GROUP BY clause, you must manually change it by selecting a different option in the Group By column of the Grid pane. (However, the Query Designer will not prevent you from choosing an option that can result in a query that will not run.)

• If you manually add a query output column to an aggregate function in either the Grid or SQL pane, the Query Designer does not automatically remove other output columns from the query. Therefore, you must remove the remaining columns from the query output or make them part of the GROUP BY clause or of an aggregate function.

When you enter a search condition into the Criteria column of the Grid pane, the Query Designer follows these rules:

• If the Group By column of the grid is not displayed (because you have not yet specified an aggregate query), the search condition is placed into the WHERE clause.
  
  
• If you are already in an aggregate query and have selected the option Where in the Group By column, the search condition is placed into the WHERE clause.
  
  
• If the Group By column contains any value other than Where, the search condition is placed in the HAVING clause.

See Also

Counting Rows in a Table | Grouping Rows in Query Results | Querying on Groups of Rows | Specifying Conditions for Groups | Summarizing and Grouping | Summarizing Values for All Rows in a Table | Summarizing Values Using Custom Expressions | Using HAVING and WHERE Clauses in the Same Query