Aggregate Functions

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 field 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. For example:

COUNT (DISTINCT last_name)

In this example, only distinct last name values are counted.

The following table lists valid aggregate functions.

Aggregate | Returns |

SUM | The total of the values in a numeric field expression. For example, SUM(SALARY) returns the sum of all salary field values. |

AVG | The average of the values in a numeric field expression. For example, AVG(SALARY) returns the average of all salary field 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 field 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 field expression. For example, MAX(SALARY) returns the maximum salary field value. |

MIN | The minimum value in any field expression. For example, MIN(SALARY) returns the minimum salary field value. |