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.

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.

Reference: Usage Notes for WHERE TOTAL

  • Any reference to a calculated value, or use of a feature that aggregates values, such as TOT.field, AVE.field, requires the use of WHERE TOTAL.
  • Fields with prefix operators require the use of WHERE TOTAL.
  • WHERE TOTAL tests are performed at the lowest sort level.
  • Alphanumeric and date literals must be enclosed in single quotation marks. Date-time literals must be in the form DT (date-time literal).
  • When you use ACROSS with WHERE TOTAL, data that does not satisfy the selection criteria is represented in the report with the NODATA character.
  • If you save the output from your report request in a HOLD file, the WHERE TOTAL test creates a field called WH$$$T1, which contains its internal computations. If there is more than one WHERE TOTAL test, each TOTAL test creates a corresponding WH$$$T field and the fields are numbered consecutively.

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