Visit our new web pages

We have launched a new extended set of web pages at www.alvechurchdata.co.uk. These old pages will stay on the server because people still have links to them but they will not be updated.

The new version of this page is at www.alvechurchdata.co.uk/hints-and-tips/foxcalculate.html.

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 SQL:

There is however an alternative which you might find easier to use. The SQL Select statement has to put its results into an array, cursor or table but the 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 but it is still very useful.

Syntax

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

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:

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:

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:

Similar commands

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

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

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.