Building the Action Diagram › How to Define Logic for Elementary Processes and Procedures › Add Assignment Actions › Add SUMMARIZE EACH Entity Action

Add SUMMARIZE EACH Entity Action

You can use the SUMMARIZE EACH statement with the SQL aggregate functions COUNT, MAX, MIN, AVERAGE, and SUM for the following purposes:

• To subdivide all selected occurrences of a set of entities into groups based on common attribute values
• To retrieve aggregate values for each group

You can also use the SUMMARIZE EACH statement to retrieve the common attribute values themselves, without selecting an aggregate function. This feature can eliminate the need for a DISTINCT clause on a READ EACH statement, because that solution does not yield the desired results.

Note: For more information about using a DISTINCT clause in READ EACH statement, see the Toolset Help.

The SUMMARIZE EACH statement is similar to the SUMMARIZE statement in that it requires a PLACING clause and allows a WHERE clause, but it also includes a WITH THE SAME clause to designate the common attributes.

The SUMMARIZE EACH statement also requires each of the common attribute views to be placed with a PLACING clause, and allows TARGETING and SORTED BY clauses.

Within SUMMARIZE EACH statements, aggregate functions that apply to groups are preceded by the word GROUP, to distinguish them from aggregates that apply to all the selected entities. All aggregate functions in the PLACING clause of a SUMMARIZE EACH GROUP statement will be GROUP functions.

The SUMMARIZE EACH statement supports the use of all ANSI standard SQL aggregate functions. This statement lets you subdivide all selected occurrences of a set of entities into groups based on common attribute values, and retrieve aggregate values for each group. The format of the SUMMARIZE EACH statement is:

SUMMARIZE EACH GROUP OF entity-action-view 
WITH THE SAME entity-action-view-attribute(s) 
TARGETING group-view FROM THE BEGINNING UNTIL FULL 
PLACING entity-action-view-attribute INTO export entity-action-view-attribute 
PLACING GROUP aggregate function INTO export entity-action-view number 
SORTED BY {ASCENDING/DESCENDING} placing-clause-source-value
WHERE selection-conditions 
action-statement-list 

entity-action-view

Contains the same views eligible for READ or READ EACH. The views that appear in the entity-view-list not be populated or modified.

WITH THE SAME clause

Identifies your groups. All common attributes designated in the WITH THE SAME clause appear in the PLACING clauses before any aggregate functions.

TARGETING clause

(Optional) Specifies the implicitly indexed repeating groups to be populate.

PLACING clause

References a WITH THE SAME attribute view and designates a modifiable attribute view to receive the results.

PLACING GROUP clause

(Optional) References an aggregate function and designates a modifiable attribute view to receive the results. The keyword, GROUP, preceding the aggregate function indicates that a new value for the function, and thus a new result table, is computed for each iteration.

SORTED BY clause

(Optional) Indicates whether the sort is ASCENDING or DESCENDING. It must reference either a PLACING (WITH THE SAME) attribute view or an aggregate function expression that occurs in a PLACING GROUP clause. The attribute-views are transient and modifiable.

WHERE clause

(Optional) Indicates the entities to be aggregated.

action-statement-list

Indicates a block of actions to execute for each group occurrence.

Note: The TARGETING, PLACING GROUP, SORTED BY, and WHERE clauses are all optional. If there is no SORTED BY clause, the groups are sorted in ascending sequence by the attributes in the WITH THE SAME clause.

The SUMMARIZE EACH statement supports the following SQL aggregate functions:

• Average
• Average Distinct
• Count Distinct
• Count Occurrences
• Maximum
• Minimum
• Sum
• Sum Distinct

Note: For more information about these aggregate functions, see the Toolset Help.

The SUMMARIZE EACH statement stops iterating when either of the following situations occurs:

• No more occurrences exist that match the selection criteria.
• A database exception occurs, regardless of whether any database exception statements exist for the SUMMARIZE EACH statement.
• If database exception statements are added to a SUMMARIZE EACH statement, they appear after any other statements that are enclosed by, and subordinate to, the SUMMARIZE EACH statement. A new "(for each successful iteration)" line also displays immediately before any such subordinate statements. Contract the line "(for each successful iteration)" to display database exceptions next to a SUMMARIZE EACH statement.

Note: For the WHERE clause in a SUMMARIZE EACH statement, views that are nullable be tested for NULL or NOT NULL. You can test these views for equivalence by using the relational operators IS EQUIVALENT TO or IS NOT EQUIVALENT TO. For more information, see the Toolset Help.