In a previous post, we discussed the concepts of functions and using scalar functions. In this post, we will focus on Aggregate functions that are used in SQL to take a series of values and return a singular result. For example, in the AdventureWorks database, there is a Sales.SalesOrderHeader table that has 31, 465 records, here is an example of the first nine records. (Note: we are only selecting four columns from the table, but there are actually twenty-five columns in this table.)
We are going to aggregate each of these columns using the code at the top of this post. Each aggregate function accepts a single parameter, more specifically the series of values from a column that needs to be aggregated. We use the COUNT function to count the SalesOrderID column (31,465), the MAX function to find the most recent order that has been placed (June 30th, 2014), the AVG function to find the average value of the freight (101.1736), and then the SUM function to find the total amount due for all the orders that have been placed (123216786.1159).
These are only four of the 15 aggregate functions built-in to SQL Server. You can find the full list here. One final note when using aggregate functions is that the results will not factor in NULL values in the aggregate results. We discuss how to handle NULL values with aggregates here.