Transformations > Aggregator transformation > Group by fields
  

Group by fields

Use group by fields to define how to group data for aggregate expressions. Configure group by fields on the Group By tab of the Properties panel.
To define a group for the aggregate expression, select the appropriate input, input/output, output, and variable 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. Data Integration typically returns the last row of each group, or the last row received, with the result of the aggregation. You can specify a particular row to be returned. For example, if you use the FIRST aggregator function, Data Integration returns the first row.
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:
STORE_ID
ITEM
QTY
PRICE
101
'battery'
3
2.99
101
'battery'
1
3.19
101
'battery'
2
2.59
101
'AAA'
2
2.45
201
'battery'
1
1.99
201
'battery'
4
1.59
301
'battery'
1
2.45
Data Integration performs the aggregate calculations on the following unique groups:
STORE_ID
ITEM
101
'battery'
101
'AAA'
201
'battery'
301
'battery'
Data Integration returns the store ID, item name, total quantity for each item by store, and total price for each item by store:
STORE_ID
ITEM
TOTAL_QTY
TOTAL_PRICE
101
'AAA'
2
4.90
101
'battery'
6
17.34
201
'battery'
5
8.35
301
'battery'
1
2.45