Calculate - an alternative to SQL

If you had to calculate the minimum, average or maximum value of a field then your first thought might be to use an SQL statement like this:

SELECT MAX (order_amt) FROM orders INTO ARRAY laMax

There is however an alternative syntax which you might find easier to use. The SQL Select statement has to put its results into an array, cursor or table but FoxPro's Calculate function will calculate various aggregate values and store the result in a memory variable. Calculate was all that we had in early versions of FoxPro before SQL was introduced but it is still very useful today.

Syntax

Being one of the older commands in the language, Calculate has a slightly unusual syntax. The simple command:

CALCULATE MAX (order_amt)

will calculate the maximum value of the order_amt field and will display it on screen. Quite useful for a quick-and-dirty output.

An extension to the command will put the result into a memory variable:

CALCULATE MAX (order_amt) TO lnMax

A further extension allows you to perform several calculations at once and store the results in several memory variables or an array. These two commands both calculate the maximum, minimum and average values. One puts the results into three memory variables, the other puts the results into an array:

CALCULATE MAX (order_amt), MIN (order_amt), AVG (order_amt) TO lnMax, lnMin, lnAvg

CALCULATE MAX (order_amt), MIN (order_amt), AVG (order_amt) TO ARRAY laStats

Options

Calculate will perform several types of aggregate calculation - the meaning of most of them are obvious:

  • AVG - Average
  • CNT - Count
  • MAX - Maximum
  • MIN - Minimum
  • STD - Standard Deviation
  • SUM - Sum
  • VAR - Variance

Each can be refined by adding a FOR or WHILE clause to the function. This example will find the average size of order sent to the UK:

CALCULATE AVG (order_amt) TO lnMax FOR country='UK'

Similar commands

COUNT and SUM are two simpler commands which do something similar:

COUNT TO lnCount

will count the rows in a table and store the result in a memory variable.

SUM (order_amt) TO lnTotal

will add the values in the order_amt field and store the result in a memory variable.

Warning

Although these commands are useful, they do have two serious shortcomings which limit their usefulness in programs.

The first is that they can only be applied to the current work area. If you need to get results from a different table then you have to store the current work area and switch to the other table. This is not difficult but forgetting to switch back afterwards is a common cause of bugs.

The other is that, like many native FoxPro commands, these three commands will move the record pointer to the end of the file as they process all the records in the table. You may have to store your position before doing the calculation so that you can get back to that record afterwards. Again, it's not difficult, but it is another potential source of bugs.

The SQL equivalents of these commands do not have these problems and are probably a better choice when you need to perform these calculations in a program.

If however you are working in the Command Window then you will find CALCULATE to be a useful tool whenever you need to do a quick aggregate calculation on the table you have open in front of you.

Scope

The CALCULATE, COUNT and SUM commands can all take a scope clause if you need to run a calculation on a particular set of records. More details on the scope of FoxPro commands here.