Caché MultiValue Query Language (CMQL) Reference
Returns the total of the values of a field.
TOTAL field [GRAND.TOTAL "label"]
clause calculates the total of the numeric values of a field. A nonnumeric field has the numeric value of 0. If a WITH
clause (or some other conditional clause) is specified, TOTAL
returns the total of the values of that field that pass the condition test.
The following example lists all of the values of the F5 field, then returns the total of these values:
LIST VOC WHERE F5 TOTAL F5
It returns 52 for the total of the F5 values. This example both lists field values and returns a total of these values.
To just return the total of value for a field, you can use the SUM
You can use the BREAK.ON
clause with TOTAL
to return subtotals. The following example returns subtotals for each distinct F5 value and a total for all F5 values. The WITH
clause eliminates records which have no value (null) for F5:
LIST VOC WITH F5 NE "" BY F5 BREAK-ON F5 TOTAL F5
The following example uses the DET-SUPP keyword to suppress the details shown by the previous example. Only the subtotals for each F5 value and the final F5 total are listed:
LIST VOC WITH F5 NE "" BY F5 BREAK-ON F5 TOTAL F5 DET-SUPP
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 a total:
LIST VOC WHERE F5 TOTAL F5 (D
48 Items listed.
The following example uses GRAND.TOTAL to label the total:
LIST VOC WHERE F5 TOTAL F5 GRAND.TOTAL "Final Tally" (D
Final Tally 52
48 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.