Aggregate functions can also be a part of a Select clause. Aggregate functions return a single value from a set of rows. An aggregate can be used with a column name (for example, AVG(SALARY)) or in combination with a more complex column expression (for example, AVG(SALARY * 1.07)). The column expression can be preceded by the DISTINCT operator. The DISTINCT operator eliminates duplicate values from an aggregate expression.

The following table lists supported aggregate functions.

Table 1. Aggregate Functions
Aggregate Returns
AVG The average of the values in a numeric column expression. For example, AVG(SALARY) returns the average of all salary column values.
COUNT The number of values in any field expression. For example, COUNT(NAME) returns the number of name values. When using COUNT with a field name, COUNT returns the number of non-NULL column values. A special example is COUNT(*), which returns the number of rows in the set, including rows with NULL values.
MAX The maximum value in any column expression. For example, MAX(SALARY) returns the maximum salary column value.
MIN The minimum value in any column expression. For example, MIN(SALARY) returns the minimum salary column value.
SUM The total of the values in a numeric column expression. For example, SUM(SALARY) returns the sum of all salary column values.

Example A

In the following example, only distinct last name values are counted. The default behavior is that all duplicate values be returned, which can be made explicit with ALL.

COUNT (DISTINCT LAST_NAME)

Example B

The following example uses the COUNT, MAX, and AVG aggregate functions:

SELECT 
     COUNT(AMOUNT) AS numOpportunities, 
     MAX(AMOUNT) AS maxAmount, 
     AVG(AMOUNT) AS avgAmount 
FROM OPPORTUNITY O INNER JOIN USER U 
     ON O.OWNERID = U.ID 
WHERE O.ISCLOSED = 'false' AND 
     U.NAME = 'MyName'