SQL Server - Aggregate Functions

Aggregate functions

will find out a calculation process on a collection of values and return a result which will be a single value.

All the aggregate functions ignore Null Values except COUNT. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

SQL Server provides the following aggregate functions:

AVG - Returns the average of the values in a group. Null values are ignored. May be followed by the OVER clause.

CHECKSUM_AGG - Returns the checksum of the values in a group. Null values are ignored. Can be followed by the OVER clause.

COUNT - Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value. May be followed by the OVER clause.

COUNT_BIG - Returns the number of items in a group. COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value. May be followed by the OVER Clause (Transact-SQL).

GROUPING - Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT list, HAVING, and ORDER BY clauses when GROUP BY is specified.

MAX - Returns the maximum value in the expression. May be followed by the OVER clause.

MIN - Returns the minimum value in the expression. May be followed by the OVER clause.

SUM - Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. May be followed by the OVER Clause (Transact-SQL).

STDEV - Returns the statistical standard deviation of all values in the specified expression. May be followed by the OVER clause.

STDEVP - Returns the statistical standard deviation for the population for all values in the specified expression. May be followed by the OVER clause.

VAR - Returns the statistical variance of all values in the specified expression. May be followed by the OVER clause.

VARP - Returns the statistical variance for the population for all values in the specified expression. May be followed by the OVER clause.