Aggregate

The Aggregate command is used to group rows based on one or more columns. When rows have equal values in the grouping column(s), the aggregation is applied over the values in the aggregation field.

Configuration

After you add an Aggregate ETL command to the ETL designer and connect an input to it, you need to provide:

• The column(s) to group by.

• The aggregation column.

• The new column name.

• The aggregation method. The supported methods are: Sum, Average, Maximum, Minimum, Count, Count Distinct Values, Count including nulls, Join Into a String, Median, and Standard deviation.

Example

In this example, we used two inputs to get the Product and OrderDetails tables from Northwind database. We used Select columns to get the product id and name from the products table and we used an Aggregate command to add a Quantities sold and chose the product id as the column to group by and sum for aggregation. Then we joined the outputs of the two commands and send the result set to the output target.