Aggregation Functions
Aggregation functions return a scalar value applying an aggregation function to a column or to an expression evaluated by iterating a table expression.
| Function | Description |
|---|---|
| APPROXIMATEDISTINCTCOUNT | Returns an estimated count of the unique values in a column. This function invokes a corresponding aggregation operation in the data source, optimized for query performance but with slightly reduced accuracy. You can use APPROXIMATEDISTINCTCOUNT with the following data sources: Azure SQL, Azure SQL Data Warehouse, BigQuery, Databricks, and Snowflake. Note that this function requires DirectQuery mode. Import mode and dual storage mode are not supported. |
| AVERAGE | Returns the average (arithmetic mean) of all the numbers in a column. |
| AVERAGEA | Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values. |
| AVERAGEX | Calculates the average (arithmetic mean) of a set of expressions evaluated over a table. |
| COUNT | Counts the number of rows in the table where the specified column has a non-blank value. |
| COUNTA | Counts the number of values in a column. |
| COUNTAX | Counts the number of values which result from evaluating an expression for each row of a table. |
| COUNTBLANK | Counts the number of blanks in a column. |
| COUNTROWS | Counts the number of rows in a table. |
| COUNTX | Counts the number of values which result from evaluating an expression for each row of a table. |
| DISTINCTCOUNT | Counts the number of distinct values in a column. |
| DISTINCTCOUNTNOBLANK | Counts the number of distinct values in a column. |
| MAX | Returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order. |
| MAXA | Returns the largest value in a column. Does not ignore logical values and text. |
| MAXX | Returns the largest value that results from evaluating an expression for each row of a table. Strings are compared according to alphabetical order. |
| MIN | Returns the smallest value in a column, or the smaller value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order. |
| MINA | Returns the smallest value in a column. Does not ignore logical values and text. |
| MINX | Returns the smallest value that results from evaluating an expression for each row of a table. Strings are compared according to alphabetical order. |
| PRODUCT | Returns the product of given column reference. |
| PRODUCTX | Returns the product of an expression values in a table. |
| SUM | Adds all the numbers in a column. |
| SUMX | Returns the sum of an expression evaluated for each row in a table. |
Last update: Jun 6, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
2018-2023 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us » Privacy Policy & Cookies