Caché MultiValue Query Language (CMQL) Reference
TOTAL clause
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Returns the total of the values of a field.
Synopsis
TOTAL field [GRAND.TOTAL "label"]
Description
The TOTAL 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 command:
SUM VOC F5
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 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 a total:
LIST VOC WHERE F5 TOTAL F5 (D
It returns:
VOC......... F5.............
 
 
***          52
 
48 Items listed.
The following example uses GRAND.TOTAL to label the total:
LIST VOC WHERE F5 TOTAL F5 GRAND.TOTAL "Final Tally" (D
It returns:
VOC......... F5.............
 
 
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.
See Also