Using QMF

G. -- Grouping data

The keyword G. groups selected rows by a specified column for the purpose of performing operations on each group. G. accumulates the results by group, but it does not order the groups. (Use AO. or DO. to ensure the desired order.)

For example, you can group by department to determine each department's average salary with the following steps:

1. Group the rows by department number (G. under DEPT).
2. Specify one average for each department (_S under SALARY and AVG._S in an unnamed column to link _S to the SALARY column).
3. Add P. in the columns you want the results selected.
4. Add AO. to put them in ascending order by department.

When you run this query:

Q.STAFF | DEPT    | SALARY |          |
--------+---------+--------+----------|
        | G.P.AO. | _S     | P.AVG._S |

QMF produces this report:

  DEPT     AVG(SALARY)
------  ---------------
    10  20865.862500000
    15  15482.332500000
    20  16071.525000000
    38  15457.110000000
    42  14592.262500000
    51  17218.160000000
    66  17215.240000000
    84  16536.750000000

Generally, G. produces one group for each set of identical values in a column. If there are null values in the column, they form a single group.

Rules for G.

• Any example element that does not refer to a G. column must have an associated column function.
• When you use grouping, you can only select data that refers to the groups. Only columns that contain G. or an aggregating function can contain P.
• A row of an example table that uses G. cannot use I., U., or D.
• If more than one column contains G., QMF groups the selected rows by each unique value of the combined columns. For example, if G. appears in both the DEPT column and the LOCATION column, each row of a group has the same DEPT value and LOCATION value.