You can select records based on the aggregate value of a field. For example, on the sum of field values, or on the average of field values, by using the WHERE TOTAL phrase. WHERE TOTAL is very helpful when you employ the aggregate display commands SUM and COUNT, and is required for fields with a prefix operator, such as AVE. and PCT.
In WHERE tests, data is evaluated before it is retrieved. In WHERE TOTAL tests, however, data is selected after all the data has been retrieved and processed. For an example, see Using WHERE TOTAL for Record Selection.
WHERE TOTAL criteria[;]
The following example sums current salaries by department.
TABLE FILE EMPLOYEE SUM CURR_SAL BY DEPARTMENT END
The output is:
DEPARTMENT CURR_SAL ---------- -------- MIS $108,002.00 PRODUCTION $114,282.00
Now, add a WHERE TOTAL phrase to the request in order to generate a report that lists only the departments where the total of the salaries is more than $110,000.
TABLE FILE EMPLOYEE SUM CURR_SAL BY DEPARTMENT WHERE TOTAL CURR_SAL EXCEEDS 110000 END
The values for each department are calculated and then each final value is compared to $110,000. The output is:
DEPARTMENT CURR_SAL ---------- -------- PRODUCTION $114,282.00
The following request extracts records for the MIS department. Then, CURR_SAL is summed for each employee. If the total salary for an employee is greater than $20,000, the values of CURR_SAL are processed for the report. In other words, WHERE TOTAL screens data after records are selected.
TABLE FILE EMPLOYEE SUM CURR_SAL BY LAST_NAME AND BY FIRST_NAME WHERE TOTAL CURR_SAL EXCEEDS 20000 WHERE DEPARTMENT IS 'MIS' END
The output is:
LAST_NAME FIRST_NAME CURR_SAL --------- ---------- -------- BLACKWOOD ROSEMARIE $21,780.00 CROSS BARBARA $27,062.00