Group by fields

To define a group for the aggregate expression, select the appropriate input, input/output, and output fields in the Aggregator transformation. You can select multiple group by fields to create a new group for each unique combination. Data Integration then performs the defined aggregation for each group.

When you group values, Data Integration produces one row for each group. If you do not group values, Data Integration returns one row for all input rows.

If the Aggregator transformation runs on an advanced cluster and the input and output fields aren't grouped by field, the transformation might not return the last row of each group with the result of the aggregation.

When you select multiple group by fields in the Aggregator transformation, Data Integration uses field order to determine the order by which it groups. The group order can affect the results. Order the group by fields to ensure the appropriate grouping. You can change the field order after you select the fields in the group.

For example, you create aggregate fields called TOTAL_QTY and TOTAL_PRICE to store the total quantity and total price for each item by store. You define the following expressions for each field:

You define STORE_ID and ITEM as the group by fields.

The input rows contain the following data:

Data Integration performs the aggregate calculations on the following unique groups:

Data Integration returns the store ID, item name, total quantity for each item by store, and total price for each item by store: