Skip to content

Aggregate functions

Aggregate functions operate on a set of rows to return a single, statistical value. You apply an aggregate to a set of rows, which may be:

  • all the rows in a table
  • only those rows specified by a WHERE clause
  • those rows created by a GROUP BY clause (see later)

The most common aggregate functions used are listed below:

Function Description
AVG( ) returns the average value of a numeric column or expression
COUNT( ) returns the number of rows that match the criteria in the WHERE clause
MAX( ) returns the largest value of the selected column or expression
MIN( ) returns the smallest value of the selected column or expression
SUM( ) returns the total sum of a numeric column or expression

In the same way that pre-defined programming functions receive parameter values, SQL aggregate functions require an expression. This expression is usually a column name, but it can be a column name together with an operator.  

The following points should be noted:

  • SUM( ) and AVG( ) can only be applied to numeric data types

  • MIN( ) and MAX( ) work with characters, numeric, and date/time datatypes

  • COUNT( ) works with all data types.

  • All aggregate functions except, COUNT( ), ignore nulls.

  • COUNT( ) always returns a positive integer or zero. The other aggregate functions return null if the set contains no rows or contains rows with only nulls.

  • An aggregate expression cannot be used in a WHERE clause.

It is however, possible to use more than one aggregate expression in a SELECT statement as shown here: 

1
2
SELECT MIN(price), MAX(price) 
FROM product;

Square brackets used in SELECTstatements refer to an attribute that has a field name of two words such as [Address of Person]

Mixing non-aggregate and aggregate expressions in a SELECTstatement is not permitted. A SELECT statement must contain either all non-aggregate expressions or all aggregate expressions.

The query below is illegal, as it mixes non-aggregate productName with the aggregate function MAX. 

1
2
SELECT productName, MAX(price) 
FROM Product;

Group By

The GROUP BY clause is used in a SELECT to form sets (or groups) of records. It does this by gathering together all records that have identical data in the specified column(s).

When used with an aggregate function, GROUP BY ensures that one result is returned for each set of grouped records.

For example:

1
2
3
SELECT resortType, COUNT(%) 
FROM Resort 
GROUP BY resortType;

In addition, GROUP BY makes it possible to mix non-aggregate and aggregate expressions for grouping columns; without GROUP BY, this is not possible.

For example, the query shown below is used to display a list of product categories together with the dearest product in each of those categories.

The category with the cheapest product is listed first.

1
2
3
4
SELECT productCategory, MAX(price) 
FROM Product
GROUP BY productCategory 
ORDER BY MAX(price) ASC;

Important

Whenever a single query has both GROUP BY and ORDER BY clauses, the GROUP BY clause always precedes the ORDER BY clause. If the clauses are reversed, a syntax error will be generated.