Export (0) Print
Expand All
Expand Minimize
1 out of 10 rated this helpful - Rate this topic

SUM Command

Totals all or specified numeric fields in the currently selected table.


SUM [eExpressionList]   [Scope] [FOR lExpression1] [WHILE lExpression2]
   [TO MemVarNameList | TO ARRAY ArrayName]   [NOOPTIMIZE]

Parameters

eExpressionList

Specifies one or more fields or field expressions to total. If you omit the field expression list, all numeric fields are totaled.

Scope

Specifies a range of records to include in the total. The scope clauses are: ALL, NEXT nRecords, RECORD nRecordNumber, and REST. For more information on scope clauses, see Scope Clauses.

The default scope for SUM is ALL records.

FOR lExpression1

Specifies that only the records for which the logical condition lExpression1 evaluates to true (.T.) are included in the total. Including FOR makes it possible for you to conditionally total records, filtering out undesired records.

Rushmore Query Optimization optimizes a SUM ... FOR command if lExpression1 is an optimizable expression. For best performance, use an optimizable expression in the FOR clause.

For more information on Rushmore optimization, see SET OPTIMIZE Command and Using Rushmore Query Optimization to Speed Data Access.

WHILE lExpression2

Specifies a condition whereby records from the current table are included in the total for as long as the logical expression lExpression2 evaluates to true (.T.).

TO MemVarNameList

Stores each total to a variable. If you specify a variable in MemVarNameList that doesn't exist, Visual FoxPro automatically creates it. Separate the variable names in the list with commas.

TO ARRAY ArrayName

Stores totals to a variable array. If the array you specify in SUM doesn't exist, Visual FoxPro automatically creates it. If the array exists and is too small to contain all the totals, the size of the array is automatically increased to accommodate the totals.

NOOPTIMIZE

Disables Rushmore optimization of SUM.

For more information, see SET OPTIMIZE Command and Using Rushmore Query Optimization to Speed Data Access.

The following example displays the totals of the in_stock and on_order fields in the products table, along with a sum of the two totals.

CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
USE products  && Opens Products table

SUM in_stock, on_order, in_stock+on_order ;
   TO gnInStock, gnOnOrder, gnUnits

CLEAR
?
? 'Total in stock : ', gnInStock  && Displays 3119.00
? 'Total on order:  ', gnOnOrder  && Displays 780.00
? 'Total # units :  ', gnUnits  && Displays 3899.00
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.