Use an aggregate field to define aggregate calculations.
When you configure an Aggregator transformation, create an aggregate field for the output of each calculation that you want to use in the data flow. You can use aggregate functions in aggregate fields. You can also use conditional clauses and nonaggregate functions.
Configure aggregate fields on the Aggregate tab of the Properties panel. When you configure an aggregate field, you define the field name, data type, precision, scale, and optional description. The description can contain up to 4000 characters. You also define the calculations that you want to perform.
When you configure aggregate fields, you can use variable fields for calculations that you want to use within the transformation. You can also include macros in aggregate and variable fields.
In advanced mode, the output is NULL if the Group by field returns a single row and the aggregate expression contains the STDDEV and VARIANCE functions. This is because Data Integration uses Spark 3.2. To get an output value of 0, set the spark.sql.legacy.statisticalAggregate session property to true in the mapping task.
Aggregate functions
You can use aggregate functions in expressions in aggregate fields.
For more information about aggregate functions, see Function Reference.
In mappings in SQL ELT mode, you use your cloud data warehouse's native aggregate functions. For more information, see the documentation for your cloud data warehouse.
Nested aggregate functions
A nested aggregate function is an aggregate function within another aggregate function.
For example, the following expression sums sales and returns the highest number:
MAX( SUM( SALES ))
You can include multiple single-level or multiple nested functions in different output fields in an Aggregator transformation. You cannot include both single-level and nested functions in an Aggregator transformation. You cannot nest aggregate functions in advanced mode.
When an Aggregator transformation contains a single-level function in any output field, you cannot use a nested function in any other field in that transformation. If you need to create both single-level and nested functions, create separate Aggregator transformations.
Conditional clauses
Use conditional clauses in the aggregate expression to reduce the number of rows used in the aggregation. The conditional clause can be any clause that evaluates to TRUE or FALSE.
For example, use the following expression to calculate the total commissions of employees who exceeded their quarterly quota: