Aggregate step (DataStage®)

In the following example, the aggregate function, average, is computed for the salary of all employees in each department. The departmentID is used as the key to group Employee items and the averageSalary is computed for each group. The result list contains an item for each distinct departmentID/grouping.

Company[]
  Employee[]
    departmentID
    salary
  result[]
    keys
      departmentID
    aggregate
      averageSalary

List to Aggregate

Specify the list that contains the elements that will be iterated on.

Scope

Defines the scope of the Aggregate function and determines the location of the output of the Aggregate step. The scope must always be the parent node of the list that is selected in the List to Aggregate field.

Aggregation Item and Aggregation Function

Select an item and the function to use for the aggregation. The function that you select must be applicable to the data type of the selected item. The following table describes the available aggregation functions.

Table 1. Aggregation functions

Function	Description
Average	Calculates the average value in the list.
Count	Counts the elements in the list. Ignore null values.
Concatenate	Concatenates all strings in the list, starting with the first element.
First	Selects first value in the list.
Last	Selects last value in the list.
Maximum	Selects the maximum value in the list. If the list contains Boolean strings, True is greater than False.
Minimum	Selects the minimum value in the list. If the list contains Boolean strings, False is less than True.
Sum	Calculates the sum of all of the values in the list.
Variance	Calculates the variance value of all of the values in the list.

Aggregation Keys

Specify aggregation keys to produce multiple aggregation rows for each unique key value.