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:
- Group the rows by department number (G. under DEPT).
- Specify one average for each department (_S under SALARY and AVG._S in an unnamed column to link _S to the SALARY column).
- Add P. in the columns you want the results selected.
- 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.
