Difference between revisions of "TOTAL"

Latest revision as of 10:00, 30 November 2009

Purpose

Total the numeric fields in the currently selected table creating a new table to contain the results

Syntax

TOTAL ON <key expression> TO <.dbf filename> | (<expC>) [<scope>]

[FIELDS <field list>]

[FOR<condition>]

[SUMMARY]

[WHILE <condition]

See Also

AVERAGE, COUNT, CREATE REPORT, SET ICACHE, SUM, UPDATE

Description

The TOTAL command creates a new table <.dbf filename> containing a record for each unique <key expression> in the active table. The file name can be substituted with a <expC>, enclosed in round brackets, which returns a valid filename. If no file extension is specified, '.dbf' is used. If no <scope> is specified, then ALL is used, unless the WHILE clause is specified, in which case the <scope> will default to REST.

FIELDS <field list>

If the FIELDS clause is specified, then only those fields specified in the <field list> will be totaled in the new table. All numeric fields copied to the new table will be totaled.

FOR <condition>

If the FOR <condition> is specified, then only those records which satisfy the <condition> will be processed.

SUMMARY

The optional SUMMARY clause will total and only copy to the new table, the fields specified in the FIELD <field list> option.

WHILE <condition>

The WHILE <condition> can be used in conjunction with the FIND, SEEK or LOCATE commands, and the REST <scope> to restrict the range of records which are processed. This can be used to optimize the performance of the TOTAL command.

If a FILTER <condition> is active, then only those records that satisfy the <condition> are processed. If SET DELETED is ON, then records that are marked for deletion are not processed.

When a table is being totaled on a particular expression, all non-numeric fields will contain the values from the first occurrence of the expression in the active table. For TOTAL to work correctly, a numeric field being totaled must be large enough to hold the totaled value.

The currently selected table does not have to be indexed or sorted on the <key expression> for TOTAL to operate. During the operation of TOTAL, the Recital/4GL builds a temporary index file for the <key expression>. If there are a large number of unique key expressions, then adjusting ICACHE will accelerate the TOTAL command. See SET ICACHE for full details.

Example

// Select wages
seek dtos(date())
total on dtos(pay_date) + emp_no to;
  (cmonth(date()) + strzero(day(date()),2) );
  while pay_date = date()
return

Products

Recital Server, Recital