2.1.6 Aggregate Functions

Table of Contents Previous Next


2 SQL Tutorial : 2.1 Getting Started : 2.1.6 Aggregate Functions

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.
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:
Aggregates are also very useful in combination with the GROUP BY clause. For example, the following query gets the highest salary 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.

2 SQL Tutorial : 2.1 Getting Started : 2.1.6 Aggregate Functions

Table of Contents Previous Next