Aggregate phrase

Identifies one or more values to calculate based on a change in an expression or a break group.

Syntax


{ AVERAGE \| COUNT \| MAXIMUM \| MINIMUM \| TOTAL \| SUB-AVERAGE \| SUB-COUNT \| SUB-MAXIMUM \| SUB-MINIMUM \| SUB-TOTAL } ... [ LABEL `aggr-label` ] [ BY `break-group` ] ...

AVERAGE

Calculates the average of all of the values of the expression in a break group and the average of all of the values of the expression in all break groups.

COUNT

Calculates the number of times the expression was counted in a break group and the count of all the values in all break groups.

MAXIMUM

Calculates the maximum of all of the values of the expression in a break group and the maximum of all the values of the expression in all break groups.

MINIMUM

Calculates the minimum of all of the values of the expression in a break group and the minimum of all the values of the expression in all break groups.

TOTAL

Calculates the subtotal of all of the values of the expression in a break group and the grand total of all of the values of the expression in all break groups. When you use default aggregates, the actual display of the grand total is deferred until the frame goes out of scope.

SUB-AVERAGE

Averages values in a break group. Does not supply an average for all records, just for those in each break group.

SUB-COUNT

Counts the number of times an expression is in a break group. Does not supply a count for all records, just for those in each break group.

SUB-MAXIMUM

Shows the maximum value of an expression in a break group. Does not supply a maximum value for all records, just for those in each break group.

SUB-MINIMUM

Shows the minimum value of an expression in a break group. Does not supply a minimum value for all records, just for those in each break group.

SUB-TOTAL

Subtotals all of the values of the expression in a break group. Does not supply a total value for all records, just for those in each break group.

BY break-group

Performs aggregation for break groups if you use the BREAK option in a FOR EACH block header.

LABEL aggr-label

Specifies a label for the aggregate value. aggr-label is a standard ABL string and can use a string attribute. The string can be translated by Translation Manager II. You can specify a maximum length attribute that is greater than the length of the longest label translation.

Examples

This procedure lists the customer information for all customers (categorized by country) and a subtotal of each country’s balance. If you use TOTAL instead of SUB-TOTAL, the AVM displays a grand total.

r-aggreg.p
FOR EACH Customer NO-LOCK BREAK BY Customer.Country: DISPLAY Customer.Name Customer.Country Customer.Balance (SUB-TOTAL BY Customer.country). END.

In the following procedure, the AVM displays the result of the COUNT aggregate even though no accumulation has occurred. In this example, COUNT displays as 0.

r-agcnt.p
DEFINE VARIABLE prntr AS LOGICAL NO-UNDO. FOR EACH Item NO-LOCK: IF prntr THEN DISPLAY Item.ItemName Item.Price(COUNT) WITH FRAME pr. END.

In the following procedure, the AVM uses “Avg. Credit Limit” and “Max. Credit Limit” as the labels for the AVERAGE and MAXIMUM aggregates respectively.

r-aglim.p
FOR EACH Customer NO-LOCK: DISPLAY Customer.Name Customer.CreditLimit (AVERAGE LABEL "Avg. Credit Limit" MAXIMUM LABEL "Max. Credit Limit" TOTAL) WITH FRAME frame1 12 DOWN. END.

Notes

By default, the AVM displays the aggregate result when the aggregate group ends, as long as the block iterates. If you want to suppress automatic display of zero aggregates, use the ACCUMULATE statement to perform the calculation and test the result with the ACCUM function before displaying the result.

When you use aggregate phrases to accumulate values within shared frames, you must include the ACCUM option in the Frame phrase. See the Frame phrase reference entry for more information.

An Aggregate phrase is designed to generate aggregate values for blocks that read forward through records in a sequential fashion. In blocks that read records in a non-sequential fashion (for example, FIND PREV, FIND FIRST, FIND LAST, etc.), an aggregate could yield unexpected values.

Avoid specifying more than one aggregate of the same type for a single field in a block. If an aggregate of the same type for a single field executes more than once during a single iteration of a block, the aggregate could yield unexpected value.

The BY phrase supports aggregates on break groups. The aggregate for a break group should reside in the block that defines the break group. Avoid positioning the aggregate in a conditional statement or sub-block in the block that defines the break group. Failure to follow these guidelines may yield unexpected values for the aggregate.

You can build your own algorithms to generate aggregates for break groups in situations that do not adhere to these guidelines. For example, you can use variables to store aggregate values for use in expressions that generate the appropriate aggregate values for break groups across blocks in a procedure.