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