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.
Lets you find the maximum value in a column.
Lets you find the minimum value in a column.
Lets you find the average value of the column.
Lets you count the number of records in the result set.
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.
Aggregate Function Usage
SELECT MAX(LineTotal) AS BiggestLineTotal
SELECT MIN(LineTotal) AS SmallestLineTotal
SELECT AVG(LineTotal) AS AverageLineTotal
SELECT COUNT(SalesOrderDetailID) AS TotalRecordsInTable
SELECT SUM(LineTotal) AS TotalOfTheOrdersInTheTable