Group Result by Columns

Use the GROUP BY tab to specify a list of columns for which you want to combine output.

For each unique set of values in the group by list, SAP Cloud Integration for data services combines or aggregates the values in the remaining columns. For example, you might want to group sales order records by order date to find the total sales ordered on a particular date.

The Aggregation and ABAP Aggregation transforms require that you specify columns to use to group the result set. All columns must either be included in a Group By or must be aggregated. To aggregate, add new columns to output with appropriate type and other info, then type in the mapping and choose an aggregate function.

To create a Group By statement:

In the Edit Data Flow view, select the transform in which you want to perform the group by.
In the Transform Details, select the Group By tab.
From the Input pane, drag one or more columns to the Column field in the Group By tab.
As needed, order the columns using the up and down arrows.
Select Save.
In the Output pane, insert a new column and enter the appropriate name, data type and other information.
In the Transform Details, in the Mapping tab, use the Aggregate function to create the mapping.

Note
Each column must be either used in the Group By or mapped with an aggregation function.

Restriction

When you use GROUP BY in an XML Map transform, you can specify either source or target columns in the grouping list.

When source columns are used, they must descend from the source schema in the current iteration rule. In addition, the path from the source schema to the column must contain no repeatable nodes.

When target columns are used, they must descend from the selected target schema. In addition, the path from the selected target schema to the column must contain no repeatable nodes.

If you specify a grouping list, then all columns in the selected output schema must be either in the grouping list or mapped to an aggregate function such as avg, count, max, min, or sum.