

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> ] )


Related Articles :


