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