Caché MultiValue Query Language (CMQL) Reference
Returns the average value of a field.
AVG field [NO.NULLS] [GRAND.TOTAL "label"]
AVERAGE field [NO.NULLS] [GRAND.TOTAL "label"]
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.
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
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
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
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.
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:
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
command returns 52 for the sum of F5, and 1.083333333 (52 / 48) for the average value of F5.