Using QMF

SUM. -- Calculate the total

The SUM. column function calculates the total of all values in a column for selected rows. It applies to a group of numbers and returns a single value for each group of numbers to which it is applied. You can use the UNQ. operator with SUM. to request that QMF use only unique values when calculating the sum. QMF ignores nulls. If all values in the specified column are null, the total is null.

You can use an arithmetic expression with SUM.. The following example calculates the total earnings (salaries plus commissions) for every selected row in Q.STAFF:

Q.STAFF | NAME | SALARY | COMM   |                  |
--------+------+--------+--------+------------------|
        |      | _S     | _C     | P. SUM. (_S+_C)  |

All the columns referred to in an unnamed column are either grouped or have a column function specified. For example, you can select the total, average, and maximum salaries by department.

When you run this query:

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

QMF produces this report:

  DEPT          SUM(SALARY)         AVG(SALARY)  MAX(SALARY)
------  -------------------  ------------------  -----------
    10             83463.45    20865.8625000000     22959.20
    15             61929.33    15482.3325000000     20659.80
    20             64286.10    16071.5250000000     18357.50
    38             77285.55    15457.1100000000     18006.00
    42             58369.05    14592.2625000000     18352.80
    51             86090.80    17218.1600000000     21150.00
    66             86076.20    17215.2400000000     21000.00
    84             66147.00    16536.7500000000     19818.00

Rules for SUM.

• You can use SUM. only on columns of numeric data type.
• In an unnamed column, specify SUM. with the example element that identifies the column to be totaled.
• SUM. can be followed by an example element, an arithmetic expression that contains at least one example element, or the UNQ. operator that is followed by an example element. If the data in a column to be totaled is defined by an arithmetic expression, enclose the expression in parentheses.
• When you apply SUM. to a column named in an example table, you must apply a column function (AVG., MIN., MAX., COUNT. or SUM.), or the G. (group) operator to every other column to select. See G. -- Grouping data.