Aggregate Functions in SQL Server

In my last two articles, I explained:

• String Functions in SQL Server
• Mathematical Functions In SQL

Today we will explain the aggregate functions. In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, bag or list.

SQL Server contains the following aggregates functions:



Now we will read each function one by one.

First create a table as in the following:

Create Table Student
(
IId int Not Null primary key,
Name Nvarchar(MAX) Not NUll,
Age Int Not Null,
Class int not Null
)

Now, insert some values into the table.

Insert Into Student
Select 1,'A',12,10 Union All
Select 2,'B',16,11 Union All
Select 3,'C',15,9 Union All
Select 4,'D',13,12 Union All
Select 5,'E',14,11 Union All
Select 6,'F',17,8 Union All
Select 7,'G',12,7 Union All
Select 8,'H',17,12

Now the table will look like the following:



AVG

The AVG function returns the average of the values in a group. Null values are ignored.

Syntax

AVG ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )

Arguments

• ALL: Applies the aggregate function to all the values. ALL is the default.
  
• DISTINCT: Specifies that AVG be done only on each unique instance of a value, regardless of how many times the value occurs.
  
• [partition_by_clause]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.
  
• Return Type: The return type is determined by the type of the evaluated result of the expression.

Expression	Return Type
Int , Smallint , int	Int
Bigint	bigint
Decimal	decimal
money, smallmoney	money
float, real	float

Example 1

SELECT AVG(age) AS Total , 'Average OF ALL VALUE' AS Discription FROM dbo.student s UNION ALL
SELECT AVG( DISTINCT( age)) AS Total , 'Average OF DISTINCT VALUE' AS Discription FROM dbo.student s

Output



Example 2

SELECT AVG(age) AS Total , class AS Class
FROM dbo.student s
GROUP BY Class

Output



Example 3

SELECT AVG(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
FROM dbo.student s

Output



COUNT

The COUNT function 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.

Syntax

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
OVER ( [ partition_by_clause ] order_by_clause )

Arguments

• ALL: Applies the aggregate function to all the values. ALL is the default.
  
• DISTINCT: Specifies that COUNT returns the number of unique not null values.
  
• [partition_by_clause ]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.
  
• Return type: INT.

Example 1

SELECT COUNT(age) AS Total , 'COUNT OF ALL VALUE' AS Discription FROM dbo.student s UNION ALL
SELECT COUNT( DISTINCT( age)) AS Total , 'COUNT OF DISTINCT VALUE' AS Discription FROM dbo.student s

Output



Example 2

SELECT COUNT(age) AS Total , class AS Class
FROM dbo.student s
GROUP BY Class

Output



Example 3

SELECT COUNT(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
FROM dbo.student s

Output



COUNT_BIG

COUNT_BIG function 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.

Syntax

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )
OVER ( [ partition_by_clause ] order_by_clause )

Arguments

• ALL: Applies the aggregate function to all the values. ALL is the default.
  
• DISTINCT: Specifies that COUNT returns the number of unique not null values.
  
• [partition_by_clause ]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.

Example 1

SELECT COUNT_BIG(age) AS Total , 'COUNT_BIG OF ALL VALUE' AS Discription FROM dbo.student s UNION ALL
SELECT COUNT_BIG( DISTINCT( age)) AS Total , 'COUNT_BIG OF DISTINCT VALUE' AS Discription FROM dbo.student s

Output



Example 2

SELECT COUNT_BIG(age) AS Total , class AS Class
FROM dbo.student s
GROUP BY Class

Output


Example 3

SELECT COUNT_BIG(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
FROM dbo.student s

Output



SUM

SUM function 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.

Syntax

SUM ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )

Arguments

• ALL: Applies the aggregate function to all values. ALL is the default.
  
• DISTINCT: Specifies that COUNT returns the number of unique not null values.
  
• [partition_by_clause ]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.
  
• Return Type: The return type is determined by the type of the evaluated result of expression.
  
  

  Expression	Return Type
  Int , Smallint	Int
  Bigint	bigint
  Decimal	decimal
  money, smallmoney	money
  float, real	float

Example 1

SELECT SUM(age) AS Total , 'SUM OF ALL VALUE' AS Discription FROM dbo.student s UNION ALL
SELECT SUM( DISTINCT( age)) AS Total , 'SUM OF DISTINCT VALUE' AS Discription FROM dbo.student s

Output



Example 2

SELECT SUM(age) AS Total , class AS Class
FROM dbo.student s
GROUP BY Class

Output



Example 3

SELECT SUM(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
FROM dbo.student s

Output



MAX

The MAX function returns the maximum value in the expression. MAX ignores any null values. For character columns, MAX finds the highest value in the collating sequence. MAX is a deterministic function when used without the OVER and ORDER BY clauses. It is non-deterministic when specified with the OVER and ORDER BY clauses.

Syntax

MAX ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )

Arguments

• ALL: Applies the aggregate function to all the values. ALL is the default.
  
• DISTINCT: Specifies that each unique value is considered. DISTINCT is not meaningful with MAX and is available for ISO compatibility only.
  
• [partition_by_clause ]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.
  
• Return Type: Returns a value as an expression.

Example 1

SELECT MAX(age) AS Total , 'MAX VALUE' AS Discription FROM dbo.student s UNION ALL
SELECT MAX( DISTINCT( age)) AS Total , 'MAX VALUE' AS Discription FROM dbo.student s

Output



Example 2

SELECT MAX(age) AS Total , class AS Class
FROM dbo.student s
GROUP BY Class

Output



Example 3

SELECT MAX(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
FROM dbo.student s

Output



MIN

The MIN function returns the minimum value in the expression. MIN ignores any null values. With character data columns, MIN finds the value that is lowest in the sort sequence. MIN is a deterministic function when used without the OVER and ORDER BY clauses. It is non-deterministic when specified with the OVER and ORDER BY clauses.

Syntax

MIN ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )

Arguments

• ALL: Applies the aggregate function to all the values. ALL is the default.
  
• DISTINCT: Specifies that each unique value is considered. DISTINCT is not meaningful with MAX and is available for ISO compatibility only.
  
• [partition_by_clause ]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.
  
• Return Type: Returns a value as an expression.

Example 1

SELECT MIN(age) AS Total , 'MIN VALUE' AS Discription FROM dbo.student s UNION ALL
SELECT MIN( DISTINCT( age)) AS Total , 'MIN VALUE' AS Discription FROM dbo.student s

Output



Example 2

SELECT MIN(age) AS Total , class AS Class
FROM dbo.student s
GROUP BY Class

Output



Example 3

SELECT MIN(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
FROM dbo.student s

Output



CHECKSUM_AGG

The CHECKSUM_AGG function returns the checksum of the values in a group. Null values are ignored. CHECKSUM_AGG can be used to detect changes in a table. The order of the rows in the table does not affect the result of CHECKSUM_AGG. Also, CHECKSUM_AGG functions may be used with the DISTINCT keyword and the GROUP BY clause. If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change.

Syntax

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

Arguments

• ALL: Applies the aggregate function to all the values. ALL is the default.
  
• DISTINCT: Specifies that CHECKSUM_AGG returns the checksum of unique values.
  
• Return Type: int

Example 1

SELECT CHECKSUM_AGG(CAST(AGE AS int)) AS CHACKSUM
FROM sTUDENT

Output



This 31 is a random value. Now we update some record from the table and calculate the CHECKSUM_AGG again.

UPDATE dbo.student
SET
AGE=28
WHERE IID=2;
SELECT CHECKSUM_AGG(CAST(AGE AS int)) AS CHACKSUM
FROM sTUDENT

Output



We can see that the value of CHECKSUM_AGG is changed that indicates some modification is performed into the table.

Example 2

SELECT IID,AGE,NAME,CLASS, CHECKSUM_AGG(CAST(AGE AS int)) AS CHACKSUM
FROM sTUDENT GROUP BY IID,AGE,NAME,CLASS

Output



Now we will update the table and calculate the checksum again.

UPDATE dbo.student
SET
AGE=22
WHERE IID%2=0;
SELECT IID,AGE,NAME,CLASS, CHECKSUM_AGG(CAST(AGE AS INT)) AS CHACKSUM
FROM STUDENT GROUP BY IID,AGE,NAME,CLASS

Output



We can observe that the value of CHECKSUM_AGG is changed after updating the value.

GROUPING

The GROUPING function 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 <select> list, HAVING and ORDER BY clauses when GROUP BY is specified.

Syntax

GROUPING ( <column_expression> )

Return Type: Int



Example

SELECT CLASS, SUM(AGE) AS [SUM] , GROUPING(CLASS) AS [GROUPING]
FROM STUDENT GROUP BY CLASS WITH ROLLUP

Output



We can see a NULL value in the last row. This NULL is in the summary row added by the ROLLUP operation. The summary row shows the SUM amounts for all CLASS groups and is indicated by 1 in the Grouping column.

GROUPING_ID

The GROUPING_ID function computes the level of grouping. GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is specified. The GROUPING_ID <column_expression> must exactly match the expression in the GROUP BY list. For example, if you are grouping by DATEPART (yyyy, <column name>), use GROUPING_ID (DATEPART (yyyy, <column name>)); or if you are grouping by <column name>, use GROUPING_ID (<column name>).

GROUPING_ID (<column_expression> [ ,...n ]) inputs the equivalent of the GROUPING (<column_expression>) return for each column in its column list in each output row as a string of ones and zeros. GROUPING_ID interprets that string as a base-2 number and returns the equivalent integer.

Syntax

GROUPING_ID ( <column_expression>[ ,...n ] )

Return Type: int

Example

SELECT AGE,SUM(CLASS) AS TOTAL , GROUPING_ID(AGE) GROUP_VALUE FROM dbo.Student s
GROUP BY AGE
WITH ROLLUP

Output



STDEV

The STDEV function returns the statistical standard deviation of all the values in the specified expression. If STDEV is used on all items in a SELECT statement, each value in the result set is included in the calculation. STDEV can be used with numeric columns only. Null values are ignored. STDEV is a deterministic function when used without the OVER and ORDER BY clauses. It is non-deterministic when specified with the OVER and ORDER BY clauses.

Syntax

STDEV ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )

Arguments

• ALL: Applies the function to all the values. ALL is the default.
  
• DISTINCT: Specifies that each unique value is considered.
  
• [partition_by_clause ]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.

Example 1

SELECT STDEV(AGE) AS STDEV_AGE , STDEV(CLASS) AS STDEV_CLASS FROM dbo.Student s;

Output

 

Example 2

SELECT STDEV(CLASS) OVER(PARTITION BY AGE ORDER BY AGE) AS STDEV_CLASS FROM dbo.Student s;

Output



VAR

The VAR function returns the statistical variance of all values in the specified expression. 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 be used with numeric columns only. Null values are ignored. VAR is a deterministic function when used without the OVER and ORDER BY clauses. It is non-deterministic when specified with the OVER and ORDER BY clauses.

Syntax

VAR ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )

Return Type: float

Arguments

• ALL: Applies the function to all the values. ALL is the default.
  
• DISTINCT: Specifies that each unique value is considered.
  
• [partition_by_clause ]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.

Example

SELECT VAR(AGE) VAR_AGE, VAR(CLASS) VAR_CLASS FROM dbo.Student s;

Output



Example

SELECT VAR(CLASS) OVER(PARTITION BY AGE ORDER BY AGE) AS VAR_CLASS FROM dbo.Student s;

Output



STDEVP

The STDEVP function returns the statistical standard deviation for the population for all values in the specified expression. If STDEVP is used on all items in a SELECT statement, each value in the result set is included in the calculation. STDEVP can be used with numeric columns only. Null values are ignored. STDEVP is a deterministic function when used without the OVER and ORDER BY clauses. It is non-deterministic when specified with the OVER and ORDER BY clauses.

Syntax

STDEVP ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )

Return Type: float 

• ALL: Applies the function to all the values. ALL is the default.
  
• DISTINCT: Specifies that each unique value is considered.
  
• [partition_by_clause ]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.

Example 1

SELECT STDEVP(AGE) STDEVP_AGE, STDEVP(CLASS) STDEVP_CLASS FROM dbo.Student s;

Output



Example 2

SELECT STDEVP(CLASS) OVER(PARTITION BY AGE ORDER BY AGE) AS STDEVP_CLASS FROM dbo.Student s;

Output



VARP

The VARP function returns the statistical variance for the population for all the values in the specified expression. If VARP is used on all items in a SELECT statement, each value in the result set is included in the calculation. VARP can be used with numeric columns only. Null values are ignored. VARP is a deterministic function when used without the OVER and ORDER BY clauses. It is non-deterministic when specified with the OVER and ORDER BY clauses.

Syntax

VARP ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )

Return Type: float

Arguments 

• ALL: Applies the function to all the values. ALL is the default.
  
• DISTINCT: Specifies that each unique value is considered.
  
• [partition_by_clause ]: partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
  
• [order_by_clause]: order_by_clause determines the logical order in which the operation is done. order_by_clause is required.

Example 1

SELECT VARP(AGE) STDEVP_AGE, VARP(CLASS) VARP_CLASS FROM dbo.Student s;

Output



Example 2

SELECT VARP(CLASS) OVER(PARTITION BY AGE ORDER BY AGE) AS VARP_CLASS FROM dbo.Student s;

Output



CHECKSUM

The CHECKSUM function returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indexes. CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indexes. If the arguments to CHECKSUM are columns and an index is built over the computed CHECKSUM value, the result is a hash index. This can be used for equality searches over the columns.

CHECKSUM satisfies the properties of a hash function. CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator.

Syntax

CHECKSUM ( * | expression [ ,...n ] )

Return Type: int



Example 1

SELECT * , CHECKSUM(*) as [CHECKSUM] FROM dbo.Student s

Output



Example 2

SELECT * , CHECKSUM( s.Class) as [CHECKSUM] FROM dbo.Student s

Output



BINARY_CHECKSUM

The BINARY_CHECKSUM function returns the binary checksum value computed over a row of a table or over a list of expressions. BINARY_CHECKSUM can be used to detect changes to a row of a table. BINARY_CHECKSUM(*), computed on any row of a table, returns the same value as long as the row is not subsequently modified.

BINARY_CHECKSUM(*) will return a different value for most, but not all changes to the row and can be used to detect most row modifications. BINARY_CHECKSUM and CHECKSUM are similar functions. They can be used to compute a checksum value on a list of expressions and the order of expressions affects the obtained value.

Syntax

BINARY_CHECKSUM ( * | expression [ ,...n ] )



Example 1

SELECT * , BINARY_CHECKSUM(*) as [CHECKSUM] FROM dbo.Student s

Output



Example 2

SELECT * , BINARY_CHECKSUM(s.Age,s.Class) as [CHECKSUM] FROM dbo.Student s

Output