Lesson 12. Aggregate Functions

While the basic arithmetic operators like + and – are technically built in functions, SQL Server has math functions for more complex operations. The functions usually operate on the entire column of data, and we will be using them when we talk about grouping and summarizing data.

Below is an opinionated list of commonly used aggregate functions.

Function	Explanation
MAX	Lets you find the maximum value in a column.
MIN	Lets you find the minimum value in a column.
AVG	Lets you find the average value of the column.
COUNT*	Lets you count the number of records in the result set.
SUM	Lets you find the total of the values in the column.

*Normally these functions are used on a single column. COUNT is the exception to that rule. Technically, you can use the wildcard operator for the function’s parameter and I frequently do, however, from a performance perspective, you should use one column. Any column will do but it is good habit to use the table’s primary key.

Examples

Aggregate Function Usage

USE AdventureWorks2016

SELECT MAX(LineTotal) AS BiggestLineTotal
FROM Sales.SalesOrderDetail
 
SELECT MIN(LineTotal) AS SmallestLineTotal
FROM Sales.SalesOrderDetail

SELECT AVG(LineTotal) AS AverageLineTotal
FROM Sales.SalesOrderDetail

SELECT COUNT(SalesOrderDetailID) AS TotalRecordsInTable
FROM Sales.SalesOrderDetail

SELECT SUM(LineTotal) AS TotalOfTheOrdersInTheTable
FROM Sales.SalesOrderDetail