Aggregate Functions in SQL Server

In my last two articles, I explained:

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:

SQL SERVER

Now we will read each function one by one.

First create a table as in the following:

  1. Create Table Student  
  2. (  
  3. IId int Not Null primary key,  
  4. Name Nvarchar(MAXNot NUll,  
  5. Age Int Not Null,  
  6. Class int not Null  
  7. )  
Now, insert some values into the table.
  1. Insert Into Student  
  2. Select 1,'A',12,10 Union All  
  3. Select 2,'B',16,11 Union All  
  4. Select 3,'C',15,9 Union All  
  5. Select 4,'D',13,12 Union All  
  6. Select 5,'E',14,11 Union All  
  7. Select 6,'F',17,8 Union All  
  8. Select 7,'G',12,7 Union All  
  9. Select 8,'H',17,12  
Now the table will look like the following:

show result

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

AVG Image

Example 1

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

Average OF ALL VALUE

Example 2
  1. SELECT AVG(age) AS Total , class AS Class   
  2. FROM dbo.student s  
  3. GROUP BY Class  
Output

AVG

Example 3
  1. SELECT AVG(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class  
  2. FROM dbo.student s  
Output

SELECT AVG

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.

COUNT

Example 1

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

COUNT OF ALL VALUE

Example 2
  1. SELECT COUNT(age) AS Total , class AS Class   
  2. FROM dbo.student s  
  3. GROUP BY Class  
Output

GROUP BY Class image

Example 3
  1. SELECT COUNT(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class  
  2. FROM dbo.student s  
Output

SELECT COUNT age

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.

COUNT BIG

Example 1

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

DISTINCT

Example 2
  1. SELECT COUNT_BIG(age) AS Total , class AS Class   
  2. FROM dbo.student s  
  3. GROUP BY Class  
Output

SELECT COUNT BIG
Example 3
  1. SELECT COUNT_BIG(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class  
  2. FROM dbo.student s  
Output

SELECT COUNT

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

SUM function

Example 1

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

SUM age

Example 2
  1. SELECT SUM(age) AS Total , class AS Class   
  2. FROM dbo.student s  
  3. GROUP BY Class  
Output

sum

Example 3
  1. SELECT SUM(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class  
  2. FROM dbo.student s  
Output

SELECT SUM

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

  1. SELECT MAX(age) AS Total , 'MAX VALUE' AS Discription FROM dbo.student s UNION ALL   
  2. SELECT MAXDISTINCT( age)) AS Total , 'MAX VALUE' AS Discription FROM dbo.student s  
Output

SELECT MAX age

Example 2
  1. SELECT MAX(age) AS Total , class AS Class   
  2. FROM dbo.student s  
  3. GROUP BY Class  
Output

GROUP BY Class

Example 3
  1. SELECT MAX(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class  
  2. FROM dbo.student s  
Output

SELECT MAX

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.

MIN

Example 1

  1. SELECT MIN(age) AS Total , 'MIN VALUE' AS Discription FROM dbo.student s UNION ALL   
  2. SELECT MINDISTINCT( age)) AS Total , 'MIN VALUE' AS Discription FROM dbo.student s  
Output

Discription

Example 2
  1. SELECT MIN(age) AS Total , class AS Class   
  2. FROM dbo.student s  
  3. GROUP BY Class  
Output

Total

Example 3
  1. SELECT MIN(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class  
  2. FROM dbo.student s  
Output

SELECT MIN

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

checksum of unique values

Example 1

  1. SELECT CHECKSUM_AGG(CAST(AGE AS int)) AS CHACKSUM  
  2. FROM sTUDENT   
Output

see result

This 31 is a random value. Now we update some record from the table and calculate the CHECKSUM_AGG again.
  1. UPDATE dbo.student  
  2. SET  
  3. AGE=28  
  4. WHERE IID=2;  
  5. SELECT CHECKSUM_AGG(CAST(AGE AS int)) AS CHACKSUM  
  6. FROM sTUDENT   
Output

CHECKSUM function

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

Example 2
  1. SELECT IID,AGE,NAME,CLASS, CHECKSUM_AGG(CAST(AGE AS int)) AS CHACKSUM  
  2. FROM sTUDENT GROUP BY IID,AGE,NAME,CLASS  
Output

Update table

Now we will update the table and calculate the checksum again.
  1. UPDATE dbo.student  
  2. SET  
  3. AGE=22  
  4. WHERE IID%2=0;  
  5. SELECT IID,AGE,NAME,CLASS, CHECKSUM_AGG(CAST(AGE AS INT)) AS CHACKSUM  
  6. FROM STUDENT GROUP BY IID,AGE,NAME,CLASS  
Output

CHECKSUM AGG

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

GROUPING

Example
  1. SELECT CLASS, SUM(AGE) AS [SUM] , GROUPING(CLASS) AS [GROUPING]  
  2. FROM STUDENT GROUP BY CLASS WITH ROLLUP  
Output

CLASS

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
  1. SELECT AGE,SUM(CLASS) AS TOTAL , GROUPING_ID(AGE) GROUP_VALUE FROM dbo.Student s  
  2. GROUP BY AGE   
  3. WITH ROLLUP  
Output

SUM class

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.

STDEV

Example 1

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

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

PARTITION

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.

VAR

Example

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

AGE

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

SELECT VAR

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.

STDEVP function

Example 1

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

STDEVP class

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

STDEVP

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.

VARP function

Example 1

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

VARP class

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

VARP

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

CHECKSUM

Example 1

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

name

Example 2
  1. SELECT * , CHECKSUM( s.Class) as [CHECKSUM] FROM dbo.Student s  
Output

Student

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

BINARY CHECKSUM


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

result

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

Output

 

Up Next
    Ebook Download
    View all
    Learn
    View all