Step 3 - Specify your group calculations

How to Create Report Totals and Subtotals › A Quick Example: Creating a Subtotal › The Report Format - Group By screen › Step 3 - Specify your group calculations

Step 3 - Specify your group calculations

To find the fastest time for women from each country, you need to compute the lowest race score in each country's group. You specify this calculation by selecting the Min (minimum) function in the row across from FINISH TIME. CA OLQ automatically calculates which woman in each country has the fastest time, and lists each group's top time.

                                                 BOSTON MARATHON
                                                     4/21/99

                                      LAST NAME    SEX    COUNTRY     FINISH TIME

                                      VANDER WYK   F      BELGIUM     2:54:12
                                      FRECETTE     F      BELGIUM     3:10:01
Group Calculation                     HELFGOTT     F      BELGIUM     3:24:31
(subgroup)                                     ┌────────────────────────────────┐
Group Level = 2───────────────────────────────► FASTEST FROM BELGIUM   :2:54:12 │
Current Group Field = COUNTRY                  └────────────────────────────────┘
Aggregate Function = MIN              DECRE        F      FRANCE      3:11:09

                                                                      FASTEST FROM FRANCE    :3:11:09
                                      BENOTTI      F      ITALY       3:12:42
                                      FUCCI        F      ITALY       3:40:37

                                                                      FASTEST FROM ITALY     :3:12:42
                                      BJORNSEN     F      SWEDEN      2:59:12

                                                                      FASTEST FROM SWEDEN    :2:59:12
                                      KLEIN        F      USA         3:09:24

                                                                      FASTEST FROM USA       :3:09:24

Group Calculations: This report uses the MIN (minimum) aggregate function to calculate the lowest time in each country's group.

When you're done, your report looks like this:

BOSTON MARATHON 05/19/99 LAST NAME SEX COUNTRY FINISH TIME ----------- --- ---------- --------------------------- VANDER WYK F BELGIUM 2:54:12 FRECETTE F BELGIUM 3:10:01 HELFGOTT F BELGIUM 3:24:31 -------- FASTEST IN BELGIUM :2:54:12 DECRE F FRANCE 3:11:09 -------- FASTEST IN FRANCE :3:11:09 BENOTTI F ITALY 3:12:42 FUCCI F ITALY 3:40:37 -------- FASTEST IN ITALY :3:12:42 BJORNSEN F SWEDEN 2:59:12 -------- FASTEST IN SWEDEN :2:59:12 KLEIN F USA 3:09:23 -------- FASTEST IN USA :3:09:23

Aggregate functions

CA OLQ provides five predefined calculations that you can apply to your report groups. These calculations are called aggregate functions. They are:

Count counts the number of rows in the report group.
Total adds up occurrences of numeric columns in the report group.
Average finds the average of the specified column in the report group.
Maximum finds the highest value of the specified column in the report group.
Minimum finds the lowest value of the specified column in the report group.

How to use aggregate functions

Aggregate functions are listed on the Report Format - Group By screen. To use them:

Make sure that the current group field is the one you want. The current group field is listed in the Group by field.
Select an aggregate function corresponding to the appropriate report column.