Average, Calculate, Count, Sum

These commands all do calculations with data from a table. AVERAGE, COUNT and SUM, as their names suggest, compute averages, count records and compute totals, respectively. CALCULATE can do all of that as well as compute minimum and maximum values, standard deviations and variances, and net present value.

For all these commands, if TALK is ON, the number of records involved is displayed in the status bar, and for SUM, AVERAGE and CALCULATE, the result is displayed to the screen. If TALK is OFF, there’s no visible display.

Usage

AVERAGE [ nExpr1 [, nExpr2 [, ... ] ] ]
        [ Scope ]
        [ FOR lForCondition ]
        [ WHILE lWhileCondition ]
        [ TO nResult1 [, nResult2 [, ... ] ]
          | TO ARRAY Result ]
        [ NOOPTIMIZE ]
COUNT   [ Scope ]
        [ FOR lForCondition ]
        [ WHILE lWhileCondition ]
        [ TO nResult ]
        [ NOOPTIMIZE ]
SUM     [ nExpr1 [, nExpr2 [, ... ] ] ]
        [ Scope ]
        [ FOR lForCondition ]
        [ WHILE lWhileCondition ]
        [ TO nResult1 [, nResult2 [, ... ] ]
          | TO ARRAY Result ]
        [ NOOPTIMIZE ]

COUNT simply counts the number of records included by the Scope, FOR and WHILE clauses. AVERAGE and SUM perform the specified calculation on each expression listed.

AVERAGE and SUM omit records containing null values in any field listed in the expression. Each expression is considered separately. If no expressions are supplied for SUM and AVERAGE, the sum or average is computed for every numeric field in the current table.

If TALK is ON, AVERAGE and SUM display their results to the active window. We really like this when we’re working interactively from the Command Window, but it drives us crazy when we’re, say, debugging a form, and go to the Command Window to test something. (ACTIVATE SCREEN prevents the output from landing on your form.)

When output is displayed in the active window, SET HEADINGS determines whether the field names or expressions appear above the results.

If nExprx includes any Currency values, the result is Currency. Otherwise, it’s Numeric.

Example

USE Customer   && TasTrade Customer table
COUNT FOR Country = "UK"  && number in the UK
* See whether UK customers are big spenders
AVERAGE Max_order_Amt FOR Country = "UK"

USE Order_Line_Items ORDER Order_Id
* Compute order total
SEEK cOrderId  && cOrderId holds an order number
SUM quantity*unit_price WHILE order_id=cOrderId TO nOrderTotal

The last example shows how SEEK and WHILE can be combined to rapidly access all the records related to a particular item.

Usage

CALCULATE Func() [, Func() [ , ... ] ]
        [ Scope ]
        [ FOR lForCondition ]
        [ WHILE lWhileCondition ]
        [ TO eResult1 [, eResult2 [, ... ] ]
          | TO ARRAY Result ]
        [ IN cAlias | nWorkArea ]
        [ NOOPTIMIZE ]

Unlike the commands above, which can perform only their particular operation (count, sum or average), CALCULATE can do a collection of different computations at once. This means you could compute, say, the total, average, minimum and maximum of some field (or expression) in one pass through a table. You are restricted to the functions provided—you can’t just put in any old FoxPro function. However, the expression passed to the function can be any valid numeric expression.

NPV() differs from the other functions used in CALCULATE. Since we don’t know beans about net present values, we’ll assume you know what a net present value is if you’re interested in this function. NPV() takes two or three parameters. The first is the interest rate per period expressed as a decimal. Remember to divide an annual rate by 12 if you’re computing monthly values. The second expression is the periodic cash flow; this is normally a field and can be negative or positive. Negative values represent expenses; positive values represent income. The optional third parameter is initial investment. If you don’t specify one, the first value of the second parameter is used and investment is assumed to occur at the end of the first period. The field value should be negative to indicate cash expended.

CALCULATE ignores null values in the function parameter.

Like SUM and AVERAGE, with TALK ON, CALCULATE displays its results in the active window, and SET HEADINGS determines whether the expression itself appears above the result.

Beginning in VFP 7, you can specify the table on which CALCULATE operates. In older versions, you need to be in the right work area. We’re not sure why the IN clause was added to CALCULATE, but not to AVERAGE, COUNT and SUM.

The Scope, FOR and WHILE clauses of all four commands determine which records are included in the result. (For more on these clauses, see “Scope, FOR, WHILE and Santa Clauses” in “Wow, What a Concept!”.)

The system variable _TALLY receives the number of records involved in the calculation (the same number that appears in the status bar). Records including nulls are counted for this purpose, even if their null values have been ignored.

COUNT is faster than CALCULATE CNT(), but CALCULATE SUM() and CALCULATE AVG() appear to be slightly faster than SUM and AVERAGE, respectively, in VFP 6 and 7. As always, your mileage may vary, so test on your hardware and under conditions similar to your final system for the authoritative answer. However, the new ability to add the IN clause to CALCULATE means that even if it were slightly slower, it’s a better choice in most situations.

NOOPTIMIZE can usually be ignored. If you think you need it, see SET OPTIMIZE.

Example

USE Products  && from TasTrade
* Display number, average unit price,
* highest unit price, lowest unit price
* for those that are available
CALCULATE CNT(),AVG(Unit_Price), ;
          MIN(Unit_Price),MAX(Unit_Price) FOR ;
   NOT Discontinued

See Also

Payment(), PV(), Select-SQL, Set Optimize