Command TOTAL Foundation

Sums data from numeric fields of a work area into a second file.

TOTAL ON <IndexKey> FIELDS <cFieldname,...> TO <cFilename> ;
 [FOR    <lForCondition>] ;
 [WHILE  <lWhileCondition>] ;
 [NEXT   <nCount>] ;
 [RECORD <xRecordID>] ;
 [REST] ;
<IndexKey> specifies the index key used to order the records in the current work area. Summation occurs until the index key provides a new value. Then a new record is added to <cFilename>.
The option FIELDS specifies a comma separated list of numeric fields whose values are totaled in records of the file <cFilename>. The field names can be specified as literals or as character expressions in parentheses.
<cFilename> is a character string containing the name of the file to which the totaled results are written. The name must contain the drive and path, if necessary. It can be specified either as a literal file name or as a character expression in parentheses. When the file name is specified without a file extension, the default extension predetermined by the current database engine (DBE) is used. The default file extension for the DBFDBE is ".DBF".
<lForCondition> is an optional logical expression which sets a condition. records in the current work area are only considered when <lForCondition> returns the value .T. (true).
<lWhileCondition> is an optional logical expression which sets a condition. Values from the work area are totaled starting with the current record only as long as <lWhileCondition> provides the value .T. (true). As soon the expression returns the value .F. (false), the operation is terminated.
<nCount> optionally specifies the number of records whose values are totaled, starting with the current record.
<xRecordID> is a record ID (for DBF files, it is the record number) which can be optionally specified. If <xRecordID> is specified, only the specified record is copied into the file <cFilename>.
The option REST specifies whether the calculation occurs only for data records from the current record up to the last record. If a condition is specified, the option ALL is used as the default value.
The option ALL includes all records from the current work area in the calculation. This is the default setting. If a condition is specified, the condition is tested for all records.

The command TOTAL generates a file which has the same structure as the file open in the current work area. The exception is memo fields, which are not included in the new file. All records in the current work area are processed sequentially. Each record where the key value <IndexKey> changes is copied to the new file. Also, the numeric values of the fields in the list <cFieldname,...> are summed in the corresponding fields of the new file until the key word changes again or a condition is no longer fulfilled.

A correct sum can only occur when the records in the current work area are sorted by the key <IndexKey>. This occurs via an index (logical sorting) or by the file being physically sorted beforehand using the command SORT. It should also be noted that the length of the numeric fields must be large enough to hold the result. Otherwise, a runtime error is caused by the numeric overflow.

records marked for deletion are considered when SET DELETED is set to OFF. If the first record with a new key value (which does not yet exist in the new file) is marked for deletion, this deletion mark is copied into the new file. records with "Deleted" status are not included when SET DELETED is set to ON.

// The example calculates the monthly sales of the year 
// 1994 using the data from an invoice file and creating 
// a sales file. 

   LOCAL n 

   INDEX ON Str(Year(InvDate),4)+Str(Month(InvDate),2) TO Temp 

   TOTAL ON Str(Year(InvDate),4)+Str(Month(InvDate),2) ; 
     FIELDS Payment ; 
        FOR Year(InvDate)==1994 ; 
         TO Sales94 

   USE Sales94 

   DO WHILE ! Eof() 
       ? "Sales in the month", CMonth( InvDate )+":", Payment 


If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.