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