Aggregate Functions in SQL Server

What are Aggregate Functions in SQL Server? This article helps you to explore various Aggregate Functions in SQL Server.
 

What are Aggregate Functions?

 
I can give different definitions.
  1. Aggregate functions are built in sql server functions.
  2. Aggregate functions are applied to sets of records rather than to a single record.
  3. Aggregate functions performs a computation on a set of values rather than on a single value.
  4. Aggregate functions uses to summarize data.
  5. Aggregate functions perform a calculation on a set of values and return a single value.
Getting Started
 
The information in multiple records are processed in a particular manner and then displayed in a single record answer.
 
Aggregate functions are often used in conjuction with GROUP BY clause.
 
Aggregate functions cannot be nested. The expression cannot be a subquery.
 
The list of built in Aggregate functions are:
 
AVG, CHECKSUM, CHECKSUM_AGG, COUNT, COUNT_BIG, GROUPING, MAX, MIN, SUM, STDEV, STDEVP, VAR, VARP.
 

AVG in SQL Server

 
AVG returns the average of the values in expression. The expression must contain numeric values. Null values are ignored.
 
The syntax: AVG ([ ALL | DISTINCT ] <expression>)
  1. select orderid, avg(UnitPrice) UnitPrice from dbo.[Order Details] group by orderid;  

CHECKSUM in SQL Server

 
This is a basic hash algorithm usually used to detect changes or consistency in data.
 
"A digit representing the sum of the correct digits in a piece of stored or transmitted digital data, against which later comparisons can be made to detect errors in the data."
 
The syntax: CHECKSUM(<expression>, [ ... n] | *)
  1. SELECT CHECKSUM(orderid, UnitPrice, quantity), orderid, UnitPrice, quantity FROM dbo.[Order Details] WHERE orderid = 10248  

CHECKSUM_AGG in SQL Server

 
The same as CHECKSUM, but the primary difference is that CHECKSUM is oriented around rows, whereas CHECKSUM_AGG is oriented around columns.
 
The syntax: CHECKSUM( [ALL | DISTINCT] <expression> )
  1. SELECT CHECKSUM_AGG(CAST(UnitPrice AS int)) FROM dbo.[Order Details]  
  2. update dbo.[Order Details] set UnitPrice = 15  
  3. where orderid = 10248 and ProductID = 11  
  4. SELECT CHECKSUM_AGG(CAST(UnitPrice AS int)) FROM dbo.[Order Details]  

COUNT in SQL Server

 
Returns the number of items in expression. The data type returned is of type int.
 
The syntax: COUNT( [ALL | DISTINCT] <expression> | * )
  1. select COUNT(*), AVG(UnitPrice) from dbo.[Order Details]  

COUNT_BIG in SQL Server

 
Returns the number of items in a group. The data type returned is of type bigint.
 
The syntax: COUNT( [ALL | DISTINCT] <expression> | * )
  1. select COUNT_BIG(*), AVG(UnitPrice) from dbo.[Order Details]  

GROUPING in SQL Server

 
MSDN : Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.
 
Function adds an extra column to the output of a SELECT statement.
 
The syntax: GROUPING(<column_name> )
  1. select orderid, sum(UnitPrice) UnitPrice, GROUPING(orderid) 'orderid'  
  2. from dbo.[Order Details] WHERE orderid = 10248  
  3. GROUP BY orderid WITH cube  

MAX in SQL Server

 
Returns the maximum value from expression. Max ignores any NULL values.
 
The syntax: MAX( [ALL | DISTINCT] <expression> )
  1. select MAX(QUANTITY) from dbo.[Order Details]  

MIN in SQL Server

 
Returns the smallest value from expression. Min ignores any NULL values.
 
The syntax: MIN( [ALL | DISTINCT] <expression> )
  1. select MIN(QUANTITY) from dbo.[Order Details]  

SUM in SQL Server

 
Returns the total of all values in expression. Sum ignores any NULL values.
 
The syntax: SUM( [ALL | DISTINCT] <expression> )
  1. select SUM(QUANTITY) from dbo.[Order Details]  

STDEV in SQL Server

 
Returns the standard deviation of all values in expression. Stdev ignores any NULL values.
 
The syntax: STDEV( <expression> )
  1. select STDEV(QUANTITY) from dbo.[Order Details]  

STDEVP in SQL Server

 
Returns the standard deviation for the population of all values in expression. Stdevp ignores any NULL values.
 
The syntax: STDEVP( <expression> )
  1. select STDEVP(QUANTITY) from dbo.[Order Details]  

VAR in SQL Server

 
Returns the variance of all values in expression. Var ignores any NULL values.
 
The syntax: VAR( <expression> )
  1. select VAR(QUANTITY) from dbo.[Order Details]  

VARP in SQL Server

 
Returns the variance for the population of all values in expression. Varp ignores any NULL values.
 
The syntax: VARP( <expression> )
  1. select VARP(QUANTITY) from dbo.[Order Details]  

Summary

 
Hope this article helped you all, Thank you.


Similar Articles