When you are in the previously described Select Fields to Show in Query dialog, you can also designate fields to summarize your numeric data. Your summary options include:
|
Option |
|
Description |
|
Example |
|---|---|---|---|---|
|
Totals |
|
Sum of a field across a range or group of records |
|
Total sales for each day. |
|
Counts |
|
Number of records in a group |
|
Count for each product in your inventory. |
|
Averages |
|
Average value of a field in a group |
|
Average sale made by each salesperson. |
|
Minimum |
|
Highest value of a field in a group |
|
Single largest order received in each region. |
|
Maximum |
|
Lowest value of a field in a group |
|
Single smallest order received in each region. |
To summarize on a numeric field, you need to designate at least two fields. One field needs to be designated as a group field and one as a summary field. A group and summary field are defined as follows:
1. Group field- the field that OT2 Query Maker will use to cluster information when building the summary value (e.g. use state to group sales records so all sales in the same state are combined into one set).
2. Summary Field- The field that you want to perform calculations on (e.g. calculate total sales by state).
An Example
In the following example, the report will summarize year to date sales (a summary field) by state (a group field). Each record in the query results will show the year to date sales for a single state.
Before you start:
· Select a query.
· Specify these two output fields:
State
Year-to-date purchases
|
1. After you have selected your query in the Query List Manager and picked your output fields, you will be taken to the query overview dialog shown at right. (See Using the Query Overview Dialog.) 2. To choose summary and group fields,
press the
|
|
|
|
|
|
The right-side column of this dialog shows the fields that will be displayed in the query results. The left-side column shows the available fields that have not yet been selected. 3. In the right-side column, highlight the State field with your mouse or arrow keys. Press the
|
|
|
|
|
|
The Group dialog shows your selected field at its top. The first three options let you indicate the level of grouping. OT2 Query Maker permits up to three different levels of group fields. (See Multiple Levels of Grouping) 4. The
option you want is
5. Press
|
|
|
The (Sum) indicator now appears on Year to date purchases. |
|
|
6. In the right column, highlight the field named: Next,
click on the
|
Year to date purchases |
|
7. Pick the
|
|
|
8. Press
|
|
|
9. Press
|
|
|
10. You are
now ready to run the query by pressing the
|
|
|
Running this query will select those records that match your filter condition and produce the summary table shown at right. Note that each state record shows the sum of all purchases in that state. |
|
OT2 Query Maker allows you to create up to three levels of grouping.
An Example
In this example, we will create a report on your customers. At the first level, we want to see the report's records grouped by customer state. But within each state, we want to see records grouped by city. Within each city, we want to see totals for each zip code.
|
1. Set State so that it is marked with a GRP 1 indicator. (See Picking Group and Summary Fields) |
|
|
2. Set City so that it is marked with a GRP 2 indicator. |
|
|
3. Set ZIP Code so that it is marked with a GRP 3 indicator. |
|
|
In the Query Overview dialog, the field list in the lower left corner will look like this: |
|
|
|
|
|
After you run the query, the report might look something like this: Note how the records are first sorted by state, then sorted by city within state, then by zip code within city. The final column shows the sales for each zip code. |
|
