Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
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:
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.
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:
CALCULATE MAX (order_amt), MIN (order_amt), AVG (order_amt) TO ARRAY laStats
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:
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.
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.
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.