Tutorials

Search…

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

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

Copy link