Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see How to Migrate to InterSystems IRIS, available on the WRC Distributions page (login required).

AVG clause

Returns the average value of a field.

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.

See Also

Feedback