Aggregate Functions

An aggregate function performs a calculation on a set of values and returns a single value. Values can be divided into groups using the GROUP BY keyword, and aggregate functions will be calculated individually per group; when no GROUP BY expressions are specified, the whole input is treated as a single group. The argument to an aggregate function can be a column reference or an expression.

With the exception of COUNT(*) all aggregate functions ignore NULL values. If an aggregate function is applied to all NULL values, its result will be NULL with the exception of COUNT – where the result will be 0 – and COUNT(*), where the result will be the number of rows.

Aggregate functions also support the DISTINCT keyword: when used, the aggregate function will be applied only to distinct values.

AVG

Returns the arithmetic mean of all the values, or only the DISTINCT values, in the expression. AVG can only be used with numeric columns. NULL values are ignored. The following query returns the average unit price of all products by category.

SELECTAVG(UnitPrice) AS[Average Price],CategoryName

FROMProductsASpINNER JOINCategoriesAScONp.CategoryID = c.CategoryID

GROUP BYCategoryName

COUNT

Returns the count of all the values, or only the DISTINCT values, in the expression. COUNT can be used with columns of any type and the return type is always integer. NULL values are ignored. The following query returns the number of orders placed by individual customers in 1997.

SELECTCOUNT(OrderID) ASOrdersCount,CustomerIDFROMOrders

WHEREyear(OrderDate)= 1997

GROUP BYCustomerID

The following query returns the count of all distinct customer titles.

SELECTCOUNT(DISTINCTContactTitle) ASTitleFROMCustomers

When COUNT(*) is used, it specifies that COUNT should count all rows, including duplicate rows or rows that contain NULL values. COUNT(*) does not take an input expression to indicate that it does not use information about any particular column during the calculation. DISTINCT cannot be used with COUNT(*). The following query returns the total number of items in the Products table.

SELECTCOUNT(*) FROMProducts

MAX

Returns the maximum of all the values in the expression. MAX can be used with numeric, date time and string columns. NULL values are ignored. The following query returns the highest unit price for all products.

SELECTMAX(UnitPrice) FROMProducts

MIN

Returns the minimum of all the values in the expression. MIN can be used with numeric, date time and string columns. NULL values are ignored. The following query returns the lowest unit price for all products.

SELECTMIN(UnitPrice) FROMProducts

MEDIAN

Returns the median of all the values, or only the DISTINCT values in the expression. MEDIAN can be used with numeric columns only. NULL values are ignored. The following query returns the median unit price for all products by category.

SELECTMEDIAN(UnitPrice) AS[Median Price],CategoryName

FROMProductsASpINNER JOINCategoriesAScONp.CategoryID = c.CategoryID

GROUP BYCategoryName

STDEV

Returns the statistical standard deviation of all the values, or only the DISTINCT values, in the expression. STDEV can only be used with numeric columns. NULL values are ignored. The following query returns the statistical standard deviation for product unit prices by category.

SELECTSTDEV(UnitPrice) AS[Price Standard Deviation],CategoryName

FROMProductsASpINNER JOINCategoriesAScONp.CategoryID = c.CategoryID

GROUP BYCategoryName

SUM

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can only be used with numeric columns. NULL values are ignored. The following query returns the total of all orders grouped by calendar year.

SELECTSUM(Quantity * UnitPrice *(1 - Discount)) ASTotal,year(OrderDate) ASYear

FROMOrderDetailsASodINNER JOINOrdersASo

ONod.OrderID = o.OrderID

GROUP BYyear(OrderDate)

VAR

Returns the statistical variance of all the values, or only the DISTINCT values, in the expression. STDEV can only be used with numeric columns. NULL values are ignored. The following query returns the statistical variance for product unit prices by category.

SELECTVAR(UnitPrice) AS[Price Variance],CategoryName

FROMProductsASpINNER JOINCategoriesAScONp.CategoryID = c.CategoryID

GROUP BYCategoryName