6.4: Totals Query

A Totals query allows you to summarize information in the database. When you summarize data from one or more tables then either:

• • • you are producing summary data for the whole table, or
    • you are producing summary data for specific groups.

For instance, you may want to know

• • • how many courses there are
    • the average of the credit hours
    • the number of courses in each department

To create a Totals query you begin by creating a simple query that retrieves all the attributes that will be needed in the summarization, and then click the Totals icon found in the upper-right hand corner of the MS Access window:

When you click the Totals icon the Grid changes to include a Total line. For each field in the grid you must choose from the drop-down:

For each field in the grid you choose one of:

• • • “Group By”: if the field is used for grouping
    • An aggregate function: if the field is to be summarized using that function. We will consider the standard set including sum, average, minimum, maximum, count.
    • “Where”: if the field has criteria to be used for selecting rows. Only rows satisfying the criteria contribute to the grouping and display of results.

Example

The simplest type of totalling query displays an aggregate over an entire set of rows. For example, to sum the credit hours over all courses in the University database one can use:

This query summarizes the entire table. The result of this query is one line displaying a sum.

Example

Typically, the use of the Totalling feature is more complicated. Consider the University database and that we need to obtain a count of the number of courses offered by each department. We begin with a query that lists the department code and any other field of the Course table (courseNo is a good choice because it can never be null – nulls are passed over when the counting of field values is performed). The query below lists the fields we need:

In the upper right-hand corner of Design View for queries you must click the Totals icon. When you click the Totals icon a new line (Total line) is added to the grid:

By default, MS Access sets each field up for grouping. To count the number of courses in each department you must click in the Total area for courseNo and change from Group By to Count:

Now you have a query that will show the value of each department code along with a count of the number of courses for the department. This query produces one row per department.

Note the choices for aggregate functions – the first 5 are part of the SQL standard: SUM, AVG, MIN, MAX, COUNT and perform a sum, average, minimum, maximum or count over the values found within a group. When a Totalling query is executed, the following actions are performed by MS Access:

1. 1. Rows are retrieved from the underlying table(s): Recall that when Where is specified in the Total line, then there is a criteria that must evaluate to true for a row to be part of this result.
   2. The retrieved rows are organized into groups where the rows forming a group have the same value for the grouping field(s).
   3. For each group aggregates are evaluated.
   4. A group can be eliminated from the results: If in the same column of the grid where Group by or an aggregate function is specified, there is some criteria given in the criteria line, then if the criteria evaluates to false, the pertinent group is excluded.