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];