# Aggregate Functions

An aggregate function is a function that calculates the total value of a group of values. Aggregate functions are commonly used with the GROUP BY clause, and with the TRANSFORM statement.

The following table lists available aggregate functions:

 Function Comments AllBranches Combines all branches of geometric objects into a single object. See the Spatial Extensions topic. AllCoords Combines all coordinates of geometric objects into a single object. See the Spatial Extensions topic. Avg Calculates the arithmetic mean of a set of numeric values. Count Calculates the number of values in a set of values of any type. First Retrieves the first value in a set of values of any type. Last Retrieves the last value in a set of values of any type. Max Calculates the maximum value in a set of numeric, string, or date values. Min Calculates the minimum value in a set of numeric, string, or date values. StDev Calculates the standard deviation of a set of numeric values. Requires at least two values. StDevP Calculates the unadjusted standard deviation of a set of numeric values. Sum Calculates the sum of a set of numeric values. UnionAll Unions all areas into a single area. See the Spatial Extensions topic. Var Calculates the variation in a set of numeric values. Requires at least two values. VarP Calculates the unadjusted variation in a set of numeric values.

All aggregate functions ignore NULL values. If the set of values is empty, the Count function returns 0, and all other aggregate functions return NULL.

Examples

This example uses the Avg aggregate function to calculate the average freight charges for orders with freight charges over \$100.

SELECT Avg([Freight]) AS [Average Freight] FROM [Orders] WHERE [Freight] > 100;

This example uses the Sum, Avg, and StDev aggregate functions to calculate the total number of units, the average number of units, and the standard deviation of the number of units ordered for each product:

SELECT [Product Name], Sum([Quantity]) AS [Total Quantity], Avg([Quantity]) AS [Typical Quantity], StDevP([Quantity]) AS [Standard Deviation] FROM [Products] INNER JOIN [Order Details] ON [Order Details].[Product ID] = [Products].[Product ID] GROUP BY [Product Name];

This example uses the Max and Min aggregate functions to calculate the maximum and minimum order adjusted for discount, and the maximum discount made on each product:

SELECT [Product Name], Min([Order Details].[Unit Price] * (1-[Discount]) * [Quantity]) AS [Min Value], Max([Order Details].[Unit Price] * (1-[Discount]) * [Quantity]) AS [Max Value], Max([Discount]) AS [Max Discount] FROM [Products] INNER JOIN [Order Details] ON [Order Details].[Product ID] = [Products].[Product ID] GROUP BY [Product Name];

This example uses the Max and Min aggregate functions to select the first and the last order for each product in 1992:

SELECT [Product Name], Min([Order Date]) AS [First Order], Max([Order Date]) AS [Last Order] FROM ([Orders] INNER JOIN [Order Details] ON [Order Details].[Order ID] = [Orders].[Order ID]) INNER JOIN [Products] ON [Products].[Product ID] = [Order Details].[Product ID] WHERE Year([Order Date]) = 1992 GROUP BY [Product Name];