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.
- Aggregate functions are built in sql server functions.
- Aggregate functions are applied to sets of records rather than to a single record.
- Aggregate functions performs a computation on a set of values rather than on a single value.
- Aggregate functions uses to summarize data.
- 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>)
- 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] | *)
- 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> )
- SELECT CHECKSUM_AGG(CAST(UnitPrice AS int)) FROM dbo.[Order Details]
- update dbo.[Order Details] set UnitPrice = 15
- where orderid = 10248 and ProductID = 11
- 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> | * )
- select COUNT(*), AVG(UnitPrice) from dbo.[Order Details]

Thangaraj SPosted Aug 25, 2019, 4:38 AM
If possible to select with max function.... Without group by clause?
Former memberPosted Jun 29, 2018, 1:55 AM
Https://dba.stackexchange.com/questions/210933/group-by-sum-based-on-under-group-in-sql-server please help for minor issue
knightsPosted Jan 4, 2011, 12:53 PM
I would like to add a note that BIT data types cant be operated with AVG, SUM, STDEV, STDEVP, VAR and VARP, besides all other numeric types. I would suggest that Definition 3 & 5 can be merged. It is a good recall about Aggregate functions.