SQL Server Helper - Aggregate Functions

Tip of the Day : SQL Server Database Design - Twitter Profile and Followers

Built-in Functions

Home > Functions > SQL Server Aggregate Functions

SQL Server Aggregate Functions

SQL Server aggregate functions perform a calculation on a set of values and return a single value. With the exception of the COUNT aggregate function, all other aggregate functions ignore NULL values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

All aggregate functions are deterministic, which means they return the same value any time that they are called by using a specific set of input values.

Function	Description
AVG	Returns the average of the values in a group. When computing for the average with the AVG aggregate function, NULL values are ignored. The AVG aggregate function can be followed by the OVER clause. Syntax: AVG ( [ ALL \| DISTINCT ] <expression> )
CHECKSUM_AGG	Returns the checksum of the values in a group. NULL values are ignored. The CHECKSUM_AGG aggregate function can be followed by the OVER clause. CHECKSUM_AGG can be used to detect changes in a table. Syntax: CHECKSUM_AGG ( [ ALL \| DISTINCT ] <expression> )
COUNT	Returns the number of items in a group. The COUNT aggregate function is similar to the COUNT_BIG aggregate function. The only difference between the two functions is their return value. The COUNT aggregate function always returns an INT data type value while the COUNT_BIG aggregate function always returns a BIGINT data type value. Both aggregate functions can be followed by the OVER clause. Syntax: COUNT ( [ [ ALL \| DISTINCT ] <expression> ] \| * )
COUNT_BIG	Returns the number of items in a group. The COUNT_BIG aggregate function is similar to the COUNT aggregate function. The only difference between the two functions is their return value. The COUNT_BIG aggregate function always returns a BIGINT data type value while the COUNT aggregate function always returns an INT data type value. Both aggregate functions can be followed by the OVER clause. Syntax: COUNT_BIG ( [ [ ALL \| DISTINCT ] <expression> ] \| * )
GROUPING	Indicates whether a specified column expression in a GROUP BY list is aggregated or not. The GROUPING aggregate function returns 1 for aggregated or 0 for not aggregated in the result set. The GROUPING aggregate function can be used only in the SELECT <select> list, HAVING and ORDER BY clauses when GROUP BY is specified. GROUPING is used to distinguish the NULL values that are returned by ROLLUP, CUBE or GROUPING SETS from standard NULL values. The NULL returned as the result of a ROLLUP, CUBE or GROUPING SETS operation is a special use of NULL. Syntax: GROUPING ( <column_expression> )
MAX	Returns the maximum value in the expression. The MAX grouping function ignores any NULL values and can be followed by the OVER clause. For character columns, MAX finds the highest value in the collating sequence. Syntax: MAX ( [ [ ALL \| DISTINCT ] <expression> ] )

Function	Description
MIN	Returns the minimum value in the expression. The MIN aggregate function ignores NULL values and can be followed by the OVER clause. With character data columns, MIN finds the value that is lowest in the sort sequence. Syntax: MIN ( [ [ ALL \| DISTINCT ] <expression> ] )
SUM	Returns the sum of all the values, or only the DISTINCT values (if the DISTINCT clause is specified), in the expression. The SUM aggregate function can only be used with numeric columns. SUM ignores NULL values and may be followed by the OVER clause. Syntax: SUM ( [ [ ALL \| DISTINCT ] <expression> ] )
STDEV	Returns the statistical standard deviation of all values in the specified expression. The STDEV aggregate function ignores NULLs and may be used followed by the OVER clause. If STDEV is used on all items in a SELECT statement, each value in the result set is included in the calculation. STDEV can only be used with numeric columns. Syntax: STDEV ( [ [ ALL \| DISTINCT ] <expression> ] )
STDEVP	Returns the statistical standard deviation for the population for all values in the specified expression. The STDEVP aggregate function ignores NULL values and may be followed by the OVER clause. If STDEVP is used on all items in a SELECT statement, each value in the result set is included in the calculation. STDEVP can only be used with numeric columns. Syntax: STDEVP ( [ [ ALL \| DISTINCT ] <expression> ] )
VAR	Returns the statistical variance of all variables in the specified expression. The VAR aggregate function ignores NULL values and may be followed by the OVER clause. If VAR is used on all items in a SELECT statement, each value in the result set is included in the calculation. VAR can only be used with numeric columns. Syntax: VAR ( [ [ ALL \| DISTINCT ] <expression> ] )
VARP	Returns the statistical variance for the population for all values in the specified expression. The VARP aggregate function ignores NULL values and may be followed by the OVER clause. If VARP is used on all items in a SELECT statement, each value in the result set is included in the calculation. VARP can only be used with numeric columns. Syntax: VARP ( [ [ ALL \| DISTINCT ] <expression> ] )