What is Built-in aggregate functions

As mentioned, Transact-SQL provides many built-in aggregate functions. Commonly used functions include:

Function Name

Syntax

Description

SUM

SUM(expression)

Totals all the non-NULL numeric values in a column.

AVG

AVG(expression)

Averages all the non-NULL numeric values in a column (sum/count).

MIN

MIN(expression)

Returns the smallest number, earliest date/time, or first-occurring string (according to collation sort rules).

MAX

MAX(expression)

Returns the largest number, latest date/time, or last-occurring string (according to collation sort rules).

COUNT or COUNT_BIG

COUNT(*) or COUNT(expression)

With (*), counts all rows, including rows with NULL values. When a column is specified as expression, returns the count of non-NULL rows for that column. COUNT returns an int; COUNT_BIG returns a big_int.

To use a built-in aggregate in a SELECT clause, consider the following example in the MyStore sample database:

SQLCopy

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

The results of this query look something like this:

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

Note that the above example summarizes all rows from the Production.Product table. We could easily modify the query to return the average, minimum, and maximum prices for products in a specific category by adding a WHERE clause, like this:

SQLCopy

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

When using aggregates in a SELECT clause, all columns referenced in the SELECT list must be used as inputs for an aggregate function, or be referenced in a GROUP BY clause.

Consider the following query, which attempts to include the ProductCategoryID field in the aggregated results:

SQLCopy

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Running this query results in the following error

Msg 8120, Level 16, State 1, Line 1

Column ‘Production.ProductCategoryID’ is invalid in the select list because it isn’t contained in either an aggregate function or the GROUP BY clause.

The query treats all rows as a single aggregated group. Therefore, all columns must be used as inputs to aggregate functions.

In the previous examples, we aggregated numeric data such as the price and quantities in the previous example. Some of the aggregate functions can also be used to summarize date, time, and character data. The following examples show the use of aggregates with dates and characters:

This query returns first and last company by name, using MIN and MAX:

SQLCopy

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

This query will return the first and last values for CompanyName in the database’s collation sequence, which in this case is alphabetical order:

MinCustomer

MaxCustomer

A Bike Store

Yellow Bicycle Company

Other functions may be nested with aggregate functions.

For example, the YEAR scalar function is used in the following example to return only the year portion of the order date, before MIN and MAX are evaluated:

SQLCopy

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

Earliest

Latest

2008

2021

The MIN and MAX functions can also be used with date data, to return the earliest and latest chronological values. However, AVG and SUM can only be used for numeric data, which includes integers, money, float and decimal datatypes.