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:
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
OutputExample 2
- SELECT AVG(age) AS Total , class AS Class
- FROM dbo.student s
- GROUP BY Class
OutputExample 3
- SELECT AVG(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
- FROM dbo.student s
OutputCOUNT
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.
SyntaxCOUNT ( { [ [ 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
OutputExample 2
- SELECT COUNT(age) AS Total , class AS Class
- FROM dbo.student s
- GROUP BY Class
OutputExample 3
- SELECT COUNT(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
- FROM dbo.student s
OutputCOUNT_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
OutputExample 2
- SELECT COUNT_BIG(age) AS Total , class AS Class
- FROM dbo.student s
- GROUP BY Class
OutputExample 3
- SELECT COUNT_BIG(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
- FROM dbo.student s
OutputSUMSUM 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
OutputExample 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
OutputMAX
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
OutputExample 2
- SELECT MAX(age) AS Total , class AS Class
- FROM dbo.student s
- GROUP BY Class
OutputExample 3
- SELECT MAX(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
- FROM dbo.student s
OutputMINThe 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
OutputExample 2
- SELECT MIN(age) AS Total , class AS Class
- FROM dbo.student s
- GROUP BY Class
OutputExample 3
- SELECT MIN(age) OVER ( PARTITION BY class ORDER BY class) AS Total , class
- FROM dbo.student s
OutputCHECKSUM_AGGThe 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
OutputThis 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
OutputWe 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
OutputNow 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
OutputWe can observe that the value of CHECKSUM_AGG is changed after updating the value.
GROUPINGThe 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
OutputWe 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
OutputSTDEVThe 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;
OutputVARThe 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;
OutputExample
- SELECT VAR(CLASS) OVER(PARTITION BY AGE ORDER BY AGE) AS VAR_CLASS FROM dbo.Student s;
OutputSTDEVP
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;
OutputExample 2
- SELECT STDEVP(CLASS) OVER(PARTITION BY AGE ORDER BY AGE) AS STDEVP_CLASS FROM dbo.Student s;
OutputVARP
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;
OutputExample 2
- SELECT VARP(CLASS) OVER(PARTITION BY AGE ORDER BY AGE) AS VARP_CLASS FROM dbo.Student s;
OutputCHECKSUM
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
OutputExample 2
- SELECT * , CHECKSUM( s.Class) as [CHECKSUM] FROM dbo.Student s
OutputBINARY_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
OutputExample 2
- SELECT * , BINARY_CHECKSUM(s.Age,s.Class) as [CHECKSUM] FROM dbo.Student s
Output