Aggregate functions in mapping data flow
APPLIES TO: 
Azure Data Factory
Azure Synapse Analytics
Tip
Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!
Data flows are available both in Azure Data Factory and Azure Synapse Pipelines. This article applies to mapping data flows. If you are new to transformations, please refer to the introductory article Transform data using a mapping data flow.
The following articles provide details about aggregate functions supported by Azure Data Factory and Azure Synapse Analytics in mapping data flows.
Aggregate function list
The following functions are only available in aggregate, pivot, unpivot, and window transformations.
| Aggregate function | Task |
|---|---|
| approxDistinctCount | Gets the approximate aggregate count of distinct values for a column. The optional second parameter is to control the estimation error. |
| avg | Gets the average of values of a column. |
| avgIf | Based on a criteria gets the average of values of a column. |
| collect | Collects all values of the expression in the aggregated group into an array. Structures can be collected and transformed to alternate structures during this process. The number of items will be equal to the number of rows in that group and can contain null values. The number of collected items should be small. |
| collectUnique | Collects all values of the expression in the aggregated group into a unique array. Structures can be collected and transformed to alternate structures during this process.The number of items will be smaller or equal to the number of rows in that group and can contain null values. The number of collected items should be small. |
| count | Gets the aggregate count of values. If the optional column(s) is specified, it ignores NULL values in the count. |
| countAll | Gets the aggregate count of values including NULLs. |
| countDistinct | Gets the aggregate count of distinct values of a set of columns. |
| countAllDistinct | Gets the aggregate count of distinct values of a set of columns including NULLs. |
| countIf | Based on a criteria gets the aggregate count of values. If the optional column is specified, it ignores NULL values in the count. |
| covariancePopulation | Gets the population covariance between two columns. |
| covariancePopulationIf | Based on a criteria, gets the population covariance of two columns. |
| covarianceSample | Gets the sample covariance of two columns. |
| covarianceSampleIf | Based on a criteria, gets the sample covariance of two columns. |
| first | Gets the first value of a column group. If the second parameter ignoreNulls is omitted, it is assumed false. |
| isDistinct | Finds if a column or set of columns is distinct. It does not count null as a distinct value |
| kurtosis | Gets the kurtosis of a column. |
| kurtosisIf | Based on a criteria, gets the kurtosis of a column. |
| last | Gets the last value of a column group. If the second parameter ignoreNulls is omitted, it is assumed false. |
| max | Gets the maximum value of a column. |
| maxIf | Based on a criteria, gets the maximum value of a column. |
| mean | Gets the mean of values of a column. Same as AVG. |
| meanIf | Based on a criteria gets the mean of values of a column. Same as avgIf. |
| min | Gets the minimum value of a column. |
| minIf | Based on a criteria, gets the minimum value of a column. |
| skewness | Gets the skewness of a column. |
| skewnessIf | Based on a criteria, gets the skewness of a column. |
| stddev | Gets the standard deviation of a column. |
| stddevIf | Based on a criteria, gets the standard deviation of a column. |
| stddevPopulation | Gets the population standard deviation of a column. |
| stddevPopulationIf | Based on a criteria, gets the population standard deviation of a column. |
| stddevSample | Gets the sample standard deviation of a column. |
| stddevSampleIf | Based on a criteria, gets the sample standard deviation of a column. |
| sum | Gets the aggregate sum of a numeric column. |
| sumDistinct | Gets the aggregate sum of distinct values of a numeric column. |
| sumDistinctIf | Based on criteria gets the aggregate sum of a numeric column. The condition can be based on any column. |
| sumIf | Based on criteria gets the aggregate sum of a numeric column. The condition can be based on any column. |
| topN | Gets the top N values for this column. |
| variance | Gets the variance of a column. |
| varianceIf | Based on a criteria, gets the variance of a column. |
| variancePopulation | Gets the population variance of a column. |
| variancePopulationIf | Based on a criteria, gets the population variance of a column. |
| varianceSample | Gets the unbiased variance of a column. |
| varianceSampleIf | Based on a criteria, gets the unbiased variance of a column. |
Related content
- List of all array functions.
- List of all cached lookup functions.
- List of all conversion functions.
- List of all date and time functions.
- List of all expression functions.
- List of all map functions.
- List of all metafunctions.
- List of all window functions.
- Usage details of all data transformation expressions.
- Learn how to use Expression Builder.