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 twenty-five columns in this table.)

SELECT SalesOrderID, OrderDate, Freight, TotalDue
FROM Sales.SalesOrderHeader

Next, we are going to aggregate the data for each of these columns. Each aggregate function accepts a single parameter, more specifically the series of values from a column that needs to be aggregated.

SELECT COUNT(SalesOrderID) as CountOrders,
	   MAX(OrderDate) as MaxOrderDate,
	   AVG(Freight) as AverageFreight,
	   SUM(TotalDue) as SumTotalDue
FROM Sales.SalesOrderHeader

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 fifteen 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.