Aggregate Functions

Aggregate functions are used to perform a calculation on one or more values, resulting in a single value. An example of a commonly used aggregate function is SUM, which is used to return the total value of a set of numeric values. Table 8-1 lists some of the more commonly used aggregate functions available in SQL Server 2005.

Function Name	Description
AVG	The AVG aggregate function calculates the average of non-NULL values in a group.
COUNT	The COUNT aggregate function returns an integer data type showing the count of rows in a group.
COUNT BIG	Works the same as COUNT, only COUNT_BIG returns a bigint data type value.
MAX	The MAX aggregate function returns the highest value in a set of non-NULL values.
MIN	The MIN aggregate function returns the lowest value in a group of non-NULL values.
SUM	The SUM aggregate function returns the total of all non-NULL values in an expression.
STDEV	The STDEV function returns the standard deviation of all values provided in the expression based on a sample of the data population.
STDEVP	The STDEVP function also returns the standard deviation for all values in the provided expression, only it evaluates the entire data population.
VAR	The VAR function returns the statistical variance of values in an expression based on a sample of the provided population.
VARP	The VARP function also returns the variance of the provided values for the entire data population of the expression.

Returning the Average of Values (AVG)
The AVG aggregate function calculates the average of non-NULL values in a group.
This first example demonstrates how to use the AVG aggregate function to return the average of
non-NULL values in a group:
-- Average Product Review by Product

SELECT ProductID,
AVG(Rating) AvgRating
FROM Production.ProductReview
GROUP BY ProductID

This second example demonstrates averaging the DISTINCT value of the StandardCost column

meaning that only unique StandardCost values are averaged:

-- Average DISTINCT Standard Cost
SELECT AVG(DISTINCT StandardCost) AvgDistinctStandardCost
FROM Production.ProductCostHistory

How It Works ?
In this recipe, the first example returned the average product rating grouped by ProductID.
The second example took an average of the DISTINCT StandardCost—meaning that only unique
StandardCost values were averaged. Without the DISTINCT keyword, the default behavior of the AVG
aggregate function is to average all values, duplicate values included.


Returning Row Counts (COUNT)
The COUNT aggregate function returns an integer data type showing the count of the rows in a group.
This example demonstrates using the COUNT aggregate function to return row counts by a group:
SELECT Shelf,
COUNT(ProductID) ProductCount
FROM Production.ProductInventory
GROUP BY Shelf
ORDER BY Shelf

If you include the DISTINCT keyword within the COUNT function parentheses, you’ll get the count
of distinct values for that column. For example:
SELECT COUNT(DISTINCT Shelf) ShelfCount
FROM Production.ProductInventory

How It Works ?
In the first example of this recipe, the number of products per shelf was counted. COUNT is the only
aggregate function that does not ignore NULL values, so had ProductID been NULL, it would have still
been included in the count. The second example demonstrated counting the number of DISTINCT
Shelf values from the Production.ProductInventory table.
If you need to count a value larger than the integer data type can hold, use the COUNT_BIG
aggregate function, which returns a bigint data type value.



Finding the Lowest and Highest Values from an Expression (MIN and MAX)
The MAX aggregate function returns the highest value and the MIN aggregate function returns the
lowest value in a group of non-NULL values. MIN and MAX can be used with numeric, character, and
datetime columns. The minimum and maximum values for character data types are determined by
using an ASCII alphabetical sort. MIN and MAX for datetime values are based on the earliest date to
the most recent date.
In this example, I’ll demonstrate how to use the MIN and MAX functions to find the lowest and
highest value in the Rating numeric column from the Production.ProductReview table:

SELECT MIN(Rating) MinRating,
MAX(Rating) MaxRating
FROM Production.ProductReview
This returns:

How It Works ?
This recipe demonstrated retrieving the minimum and maximum Rating values from the
Product.ProductReview table. As with other aggregate functions, had there also been non-aggregated
GROUP BY clause.



Returning the Sum of Values (SUM)
The SUM aggregate function returns the total of all non-NULL values in an expression.
This example demonstrates how to use the SUM aggregate function to total the value of the
TotalDue column for each AccountNumber:

SELECT AccountNumber,
SUM(TotalDue) TotalDueBySalesOrderID
FROM Sales.SalesOrderHeader
GROUP BY AccountNumber
ORDER BY AccountNumber

This returns the following abridged results:

How It Works ?
In this recipe, the TotalDue column was totaled by AccountNumber. Since AccountNumber wasn’t aggregated itself, it was included in the GROUP BY clause. It was also included in the ORDER BY clause, in
order to order the grouped results.


Using Statistical Aggregate Functions (VAR,VARP ,STDEV and STDEVP)
In this recipe, I’ll demonstrate using the statistical functions VAR, VARP, STDEV, and STDEVP. The VAR function returns the statistical variance of values in an expression based on a sample of the provided population (the VARP function also returns the variance of the provided values for the entire data population of the expression). This first example returns the statistical variance of the TaxAmt value for all rows in the
Sales.SalesOrderHeader table:

SELECT VAR(TaxAmt) Variance_Sample,
VARP(TaxAmt) Variance_EntirePopulation
FROM Sales.SalesOrderHeader

The STDEV function returns the standard deviation of all the values provided in the expression, based on a sample of the data population. The STDEVP function also returns the standard deviation for all values in the provided expression, only it evaluates the entire data population instead. This example returns the statistical standard deviation of the UnitPrice value for all rows in the
Sales.SalesOrderDetail table:

SELECT STDEV(UnitPrice) StandDevUnitPrice,
STDEVP(UnitPrice)StandDevPopUnitPrice
FROM Sales.SalesOrderDetail

How It Works ?
Although the use of each statistical function varies, the implementation is similar. Specifically, in
this example, each function takes a value expression, using a column name from the table. The
function then acts on the set of data (zero or more rows) using the column specified in the SELECT
clause, returning a single value.