MODIFY WINDOW | Hackers Guide to Visual FoxPro 7.0

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 ]

Parameter	Value	Meaning
nExpr1, nExpr2, ...	Any numeric type	The expression (involving one or more fields) to be summed or averaged.
nResult1, nResult2, ...	Numeric	Variables to hold the averages, counts or sums.
Result	Numeric Array	Array to hold the averages or sums. A one-dimensional array is created if it doesn't exist. The array is expanded if it's too small.

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 ]

Parameter	Value	Meaning
Func	AVG( nExpr )	Computes the average of the non-null values of the enclosed numeric expression.
	CNT( [ nExpr ] )	Counts the number of records that match the conditions. If nExpr is specified, counts the number of records meeting the conditions for which nExpr isn't null. Note the use of nExpr here is undocumented.
	MAX( eExpr )	Finds the maximum of the non-null values for the specified expression. The expression may be Character, Date, Datetime or any numeric type.
	MIN( eExpr )	Finds the minimum of the non-null values for the specified expression. The expression may be Character, Date, Datetime or any numeric type.
	NPV( nExpr1, nExpr2 [ , nExpr3 ] )	Computes the net present value of a series of cash flows. See below for more explanation.
	STD( nExpr )	Computes the standard deviation of the non-null values of the enclosed numeric expression.
	SUM( nExpr )	Computes the total of the enclosed numeric expression.
	VAR( nExpr )	Computes the variance of the non-null values of the enclosed numeric expression.
uResult1, uResult2, ...	Same type as passed expression	Variables to hold the results.
Result	Array	Array to hold the results. A one-dimensional array is created if it doesn't exist. The array is expanded if it's too small.
cAlias	Character	Perform the calculation on the table open with the specified alias.
cAlias	Omitted	If nWorkArea is also omitted, perform the calculation on the table open in the current work area.
nWorkArea	Numeric	Perform the calculation on the table open in the specified work area.
nWorkArea	Omitted	If cAlias is also omitted, perform the calculation on the table open in the current work area.

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