Aggregate functions are one set of built-in functions within SAP Data Services.
By using aggregate functions in the Query transform, you create a whole new data set in your target.
Aggregate Functions Overview
Aggregate functions:
- Perform calculations on numeric values and some non-numeric data.
- Require a set of values to operate.
- Generate a single value from a set of values.
- Are most commonly called from within a Query transform, not from custom functions or scripts.
- Use the data set specified by the expression in the Group By tab of a query.
When you use an aggregate function, except if you want only one row as a result, you need to specify a Group By clause and all the elements in this Group By clause must also be present in the Schema Out.
You can specify more than one aggregated measure in the same Query transform if they all have the same grouping criteria.
Built-In Aggregate Functions
There are six different aggregation functions you can use in your queries.
List of Aggregate Functions
The table lists the names and descriptions of built-in aggregate functions:
| Function | Description |
|---|---|
| avg | Calculate the average of a given set of values. |
| count | Count the number of values in a table column. |
| count_distinct | Count the number of distinct non-null values in a table column. |
| max | Return the maximum value from a list. |
| min | Return the minimum value from a list. |
| sum | Calculate the sum of a given set of values. |