Difference between revisions of "TOTAL"
Yvonne.milne (Talk | contribs) m (1 revision) |
Barrymavin (Talk | contribs) (→See Also) |
||
| (8 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
==Purpose== | ==Purpose== | ||
Total the numeric fields in the currently selected table creating a new table to contain the results | Total the numeric fields in the currently selected table creating a new table to contain the results | ||
| − | |||
==Syntax== | ==Syntax== | ||
| Line 13: | Line 12: | ||
[WHILE <condition] | [WHILE <condition] | ||
| − | |||
| − | |||
==See Also== | ==See Also== | ||
| − | [[AVERAGE]], [[COUNT]], [[ | + | [[AVERAGE]], [[COUNT]], [[SUM]], [[UPDATE]] , [[CALCULATE]] |
| − | + | ||
==Description== | ==Description== | ||
| Line 33: | Line 29: | ||
====WHILE <condition>==== | ====WHILE <condition>==== | ||
| − | The WHILE <condition> can be used in conjunction with the | + | The WHILE <condition> can be used in conjunction with the 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. | 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. | ||
| Line 39: | Line 35: | ||
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. | 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 | + | 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 Lianja/4GL builds a temporary index file for the <key expression>. |
| − | + | ||
==Example== | ==Example== | ||
| Line 52: | Line 47: | ||
</code> | </code> | ||
| − | |||
| − | |||
| − | |||
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:Commands]] | [[Category:Commands]] | ||
| − | [[Category: | + | [[Category:NoSQL Commands]] |
| − | + | ||
| − | + | ||
Latest revision as of 21:35, 25 December 2020
Contents
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, SUM, UPDATE , CALCULATE
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 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 Lianja/4GL builds a temporary index file for the <key expression>.
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
