# Aggregate Functions v10

Like most other relational database products, Advanced Server supports aggregate functions. An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the `COUNT`

, `SUM`

, `AVG`

(average), `MAX`

(maximum), and `MIN`

(minimum) over a set of rows.

As an example, the highest and lowest salaries can be found with the following query:

If we wanted to find the employee with the largest salary, we may be tempted to try:

This does not work because the aggregate function, `MAX`

, cannot be used in the `WHERE`

clause. This restriction exists because the `WHERE`

clause determines the rows that will go into the aggregation stage so it has to be evaluated before aggregate functions are computed. However, the query can be restated to accomplish the intended result by using a *subquery*:

The subquery is an independent computation that obtains its own result separately from the outer query.

Aggregates are also very useful in combination with the `GROUP BY`

clause. For example, the following query gets the highest salary in each department.

This query produces one output row per department. Each aggregate result is computed over the rows matching that department. These grouped rows can be filtered using the `HAVING`

clause.

This query gives the same results for only those departments that have an average salary greater than 2000.

Finally, the following query takes into account only the highest paid employees who are analysts in each department.

There is a subtle distinction between the `WHERE`

and `HAVING`

clauses. The `WHERE`

clause filters out rows before grouping occurs and aggregate functions are applied. The `HAVING`

clause applies filters on the results after rows have been grouped and aggregate functions have been computed for each group.

So in the previous example, only employees who are analysts are considered. From this subset, the employees are grouped by department and only those groups where the average salary of analysts in the group is greater than 2000 are in the final result. This is true of only the group for department 20 and the maximum analyst salary in department 20 is 3000.00.