Query That Summarizes Data

Getting Started with OpenOffice 4

Chapter 7: Getting Started with Base

< Previous Section

Next Section >

• What is Base?
• Planning a database
• Creating a new database
• Creating database tables
  • Using the wizard to create a table
  • Creating a table by copying an existing table
  • Creating tables in design view
  • Creating auxiliary tables for data entry
• Defining relationships
• Creating a database form
  • Using the wizard to create a simple form
  • Using the wizard to make a form with a subform
  • Laying out a form with multiple controls
  • Creating forms and subforms in design view
• Accessing other data sources
• Using data sources in Apache OpenOffice
• Creating queries
  • Using the wizard to create a query
  • Using the design view to create a query
  • Make a query with a calculated field
  • Query with two related tables
  • Query that summarizes data
  • Query summarizing over a calculated field
• Creating reports
  • Simple static report
  • Grouped dynamic report

< Previous Page

Next Page >

Query That Summarizes Data

All the queries we have written so far display all the rows that meet the query criteria. It is very common to want a summary of the data that displays quantities like the sum or the average of a field calculated over some group of rows. For example, we might want the sum of sales for each person. Writing such a query can be done using the Function row of the query definition table in Design View.

Step 1: Start the query in Design View

From the main Base window, click Queries in the left pane and then Create Query in Design View from the Tasks pane.

Step 2: Add tables to the query

In the Add Query or Table dialog, click on People and then the Add button and do the same for the Sales table. Click  Close .

Step 3: Add fields to the query

Double-click on the FirstName and LastName from the People table and Amount from the Sales table to add them to the query definition table.

Step 4: Set the grouping and the sum function

In the Function row of the query definition table, use the drop-down list to set FirstName and LastName to Group and set Amount to Sum. Also set the Alias of the Amount column to Sales_Sum. The final settings of the query definition table are shown in Figure 197.

Figure 197: Query summing Amount for each person

In a query that uses one or more summarizing functions, also known as aggregating functions, every column must have an entry in the Function row, either as Group or one of the other functions.