Aggregate functions (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

An aggregate function in the Microsoft SQL Database Engine performs a calculation on a set of values, and returns a single value.

• Except for COUNT(*), aggregate functions ignore NULL values.
• Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
• Unless otherwise noted, aggregate functions are deterministic. In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values. For example, APPROX_MEDIAN and APPROX_QUANTILE are not deterministic. See Deterministic and nondeterministic functions for more information about function determinism.
• The OVER clause can follow all aggregate functions, except the STRING_AGG, GROUPING, or GROUPING_ID functions.
• Use aggregate functions as expressions only in the select list of a SELECT statement (either a subquery or outer query), or in a HAVING clause.

The Microsoft SQL Database Engine provides the following aggregate functions in some or all platforms. Refer to each syntax article for applicable platforms.

• ANY_VALUE - Picks an arbitrary value from the rows in a group and returns it.
• APPROX_COUNT_DISTINCT - Returns an approximate count of distinct non-null values using a memory-efficient algorithm.
• APPROX_MEDIAN - Returns an approximate median value for a set of values.
• APPROX_PERCENTILE_CONT - Returns an approximate percentile value using continuous interpolation.
• APPROX_PERCENTILE_DISC - Returns an approximate percentile value selected from the actual data values.
• APPROX_QUANTILE - Returns an approximate quantile value for a specified quantile position.
• AVG - Calculates the average of the values in a group.
• CHECKSUM_AGG - Returns a checksum value computed over the values in a group.
• COUNT - Returns the number of rows or non-null values in a group.
• COUNT_BIG - Returns the number of rows or non-null values in a group as a bigint type.
• GROUPING - Indicates whether a column value in a result row was aggregated by a grouping operation.
• GROUPING_ID - Returns a bitmask that identifies which columns were aggregated in a grouping set.
• PRODUCT - Returns the product of the non-null values in a group.
• MAX - Returns the maximum value in a group.
• MEDIAN - Returns the median value of the values in a group.
• MIN - Returns the minimum value in a group.
• PERCENTILE_CONT - Calculates a percentile based on a continuous distribution of the column value.
• PERCENTILE_DISC - Computes a specific percentile for sorted values in an entire rowset or within a rowset's distinct partitions.
• QUANTILE - Returns the value corresponding to a specified quantile within a group.
• STDEV - Returns the sample standard deviation of the values in a group.
• STDEVP - Returns the population standard deviation of the values in a group.
• STRING_AGG - Concatenates string values from multiple rows into a single string.
• SUM - Returns the sum of the values in a group.
• VAR - Returns the sample variance of the values in a group.
• VARP - Returns the population variance of the values in a group.

Related content

• What are the SQL database functions?
• SELECT - OVER clause (Transact-SQL)