Selecting Aggregate Function Expressions

An aggregate function returns one value for a set of queried rows. This value depends on the set of rows that the WHERE clause of the SELECT statement qualifies. In the absence of a WHERE clause, the aggregate functions take on values that depend on all the rows that the FROM clause forms.

Examples that follow show aggregate functions in a projection list:

SELECT SUM(total_price) FROM items WHERE order_num = 1013;
SELECT COUNT(*) FROM orders WHERE order_num = 1001;
SELECT MAX(LENGTH(fname) + LENGTH(lname)) FROM customer;

If the Projection clause does not specify the DISTINCT or UNIQUE keyword of the SELECT statement, however, the query can include one or more aggregate functions that include the DISTINCT or UNIQUE keyword as the first specification of the argument lists:

SELECT SUM(DISTINCT total_price) FROM items WHERE order_num = 1013;
SELECT COUNT(DISTINCT *) FROM orders WHERE order_num = 1001;
SELECT MAX(LENGTH(fname) + LENGTH(UNIQUE lname)) FROM customer;

The database server issues an error, however, if both the Projection clause and an aggregate function expression specify the DISTINCT or UNIQUE keyword in the same query.

Aggregate expressions in grid queries

For grid queries that include aggregate function expressions, you must specify the GRID clause in a subquery, if the value of the aggregate expression that each grid server calculates is a quotient whose denominator varies across the participating grid servers.

SQL aggregate expressions and OLAP window aggregates

Do not confuse SQL aggregate functions with the On-Line Analytical Processing (OLAP) window aggregation functions, which are a different category of functions.

When an aggregate function expression is immediately followed by the OVER clause, the database server attempts to interpret it as an OLAP aggregation function. Some OLAP aggregation functions have the same names (and support a subset of the same syntax) as SQL aggregate functions, but the two types of functions have different behavior.

An SQL aggregate function can be nested inside an OLAP aggregation function. For example, the following query is valid in a context where dollars is a column in the sales table:

SELECT AVG(SUM(dollars)) OVER() FROM sales;

In the example above, the SUM function is an SQL aggregate function and the containing AVG function is an OLAP window function. The order of query processing dictates that OLAP functions are always calculated after grouping and aggregation operations but before the final ORDER BY operation.