AVG clause
Synopsis
AVG field [NO.NULLS] [GRAND.TOTAL "label"] AVERAGE field [NO.NULLS] [GRAND.TOTAL "label"]
Description
The AVG and AVERAGE keywords are synonyms.
The AVG clause calculates the average value for a numeric field. If a WITH clause (or some other conditional clause) is specified, AVG returns the average of the values of that field that pass the condition test.
By default, fields containing nonnumeric values or NULL are counted in determining the average value. NULL and nonnumeric fields are treated as having the numeric value of 0. The optional NO.NULLS keyword removes items without a value (NULL) from the count of items used to calculate the average.
AVG calculates an average to nine decimal places. Leading and trailing zeros are suppressed.
Multiple CMQL clauses may be specified in any order. The order of application of CMQL clauses is always the same. The WITH clause (or other condition test clause) is applied first. The SAMPLED clause (if present) is applied next, then the SAMPLE clause (if present), then the AVG clause.
GRAND.TOTAL Keyword
GRAND.TOTAL and GRAND-TOTAL are synonyms.
By default, in a horizontal display the summation line is not labeled; it is indicated by the “***” placeholder. You can use the GRAND.TOTAL keyword to assign a label name to the summation line. GRAND.TOTAL has no effect on vertical display format.
The following example shows the default labeling of an average:
LIST SALES WITH AMOUNT > "$100.00" AVG AMOUNT (D
It returns:
SALES..... AMOUNT......... *** $504.34 24423 Items listed.
The following example uses GRAND.TOTAL to label the average:
LIST SALES WITH AMOUNT > "$100.00" AVG AMOUNT GRAND.TOTAL "AvgAmt" (D
It returns:
SALES..... AMOUNT......... AvgAmt $504.34 24423 Items listed.
Note that the total, maximum, minimum, and average values are all listed as columns in the same summation line. They are listed in the order in which the clauses were specified. A single GRAND.TOTAL keyword specifies the label for the entire summation line. GRAND.TOTAL does not have to directly follow the clause(s) that it affects. If you specify multiple GRAND.TOTAL keywords, the last one specified is used.
Examples
The following example returns both the total value and the average value of the field F5; in this case the VOC contains 478 records, most of which have no value for the F5 field:
SUM VOC F5 AVG F5
It returns 52 for the sum of F5, and .108786611 (52 / 478) for the average value of F5.
The following example returns both the total values and the average value of the field F5, when NULLs are not counted. As can be seen from the COUNT command, the VOC contains 48 records in which F5 has a value that includes at least one character.
COUNT VOC F5 WITH F5 LIKE "...0X"
SUM VOC F5 AVG F5 NO.NULLS
The SUM command returns 52 for the sum of F5, and 1.083333333 (52 / 48) for the average value of F5.
