Where you can use aggregate functions

The aggregate functions can be used in a select list, as in the previous examples, or in the HAVING clause of a select statement that includes a GROUP BY clause. See The HAVING clause: selecting groups of data.

You cannot use aggregate functions in a WHERE clause or in a JOIN condition. However, a SELECT statement with aggregate functions in its select list often includes a WHERE clause that restricts the rows to which the aggregate is applied.

If a SELECT statement includes a WHERE clause, but not a GROUP BY clause, an aggregate function produces a single value for the subset of rows that the WHERE clause specifies.

Whenever an aggregate function is used in a SELECT statement that does not include a GROUP BY clause, it produces a single value. This is true whether it is operating on all the rows in a table or on a subset of rows defined by a where clause.

You can use more than one aggregate function in the same select list, and produce more than one scalar aggregate in a single SELECT statement.

Aggregate functions and outer references

SQL Anywhere follows SQL/2008 standards for clarifying the use of aggregate functions when they appear in a subquery. These changes affect the behavior of statements written for previous versions of the software: previously correct queries may now produce error messages, and result sets may change.

When an aggregate function appears in a subquery, and the column referenced by the aggregate function is an outer reference, the entire aggregate function itself is now treated as an outer reference. This means that the aggregate function is now computed in the outer block, not in the subquery, and becomes a constant within the subquery.

The following restrictions apply to the use of outer reference aggregate functions in subqueries:

The outer reference aggregate function can only appear in subqueries that are in the SELECT list or HAVING clause, and these clauses must be in the immediate outer block.
Outer reference aggregate functions can only contain one outer column reference.
Local column references and outer column references cannot be mixed in the same aggregate function.

Some problems related to the new standards can be circumvented by rewriting the aggregate function so that it only includes local references. For example, the subquery (SELECT MAX(S.y + R.y) FROM S) contains both a local column reference (S.y) and an outer column reference (R.y), which is now illegal. It can be rewritten as (SELECT MAX(S.y) + R.y FROM S). In the rewrite, the aggregate function has only a local column reference. The same sort of rewrite can be used when an outer reference aggregate function appears in clauses other than SELECT or HAVING.

Example

The following query produced valid results in earlier versions of SQL Anywhere:

SELECT Name, 
      ( SELECT SUM( p.Quantity )
           FROM SalesOrderItems )
   FROM Products p;

Name	SUM(p.Quantity)
Tee shirt	30,716
Tee shirt	59,238

In later versions, the same query produces the error message SQL Anywhere Error -149: Function or column reference to 'name' must also appear in a GROUP BY. The reason that the statement is no longer valid is that the outer reference aggregate function sum(p.Quantity) is now computed in the outer block. In later versions, the query is semantically equivalent to the following (except that Z does not appear as part of the result set):

SELECT Name,
       SUM( p.Quantity ) AS Z,
       ( SELECT Z 
            FROM SalesOrderItems )
   FROM Products p;

Since the outer block now computes an aggregate function, the outer block is treated as a grouped query and column name must appear in a GROUP BY clause to appear in the SELECT list.

Discuss this page in DocCommentXchange.