Selection Based on Aggregate Values

How to:

Reference:

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.


Top of page

x
Syntax: How to Select Records With WHERE TOTAL
WHERE TOTAL criteria[;]

where:

criteria
Are the criteria for selecting records to include in the report. The criteria must be defined in a valid expression that evaluates as true or false (that is, a Boolean expression). Expressions are described in detail in Using Expressions. Operators that can be used in WHERE expressions (such as, IS and GT) are described in Operators Supported for WHERE and IF Tests.
;
Is an optional semicolon that can be used to enhance the readability of the request. It does not affect the report.

Top of page

x
Reference: Usage Notes for WHERE TOTAL


Example: Using WHERE TOTAL for Record Selection

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


Example: Combining WHERE TOTAL and WHERE for Record Selection

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

WebFOCUS