Selecting correlated aggregates in subqueries

In a subquery, an aggregate expression with a column operand that was declared in a parent query block is called a correlated aggregate. The column operand is called a correlated column reference.

SELECT statements and other DML statements can include subqueries with aggregate expressions whose operands reference columns that were declared in a parent query block. When a subquery contains an aggregate with a correlated column reference, the database server evaluates that aggregate in the parent query block where the correlated column reference was declared.

If an aggregate expression in a subquery contains correlated references to columns from more than one parent query block, the correlated aggregate is evaluated in the parent that is nearest to the subquery in the lexical order of query blocks.

Examples of aggregates with correlated column references

In the following example, the database server evaluates the correlated aggregate COUNT(n.j) in the parent query block, which declared the table alias n that appears in the correlated column reference of the subquery:

CREATE TABLE tab(i INT); 
CREATE TABLE tab2(j INT); 
. . . 
SELECT m.i, 
       (SELECT COUNT(n.j) 
           FROM tab2 WHERE j=15) AS o 
    FROM tab m, tab2 n GROUP BY 1;

The only exception to the rule illustrated above occurs when the correlated aggregate is an argument to an aggregate in the parent query block. In this scenario, the aggregate is evaluated in the subquery, if the correlated column reference comes from the same query block where the outer aggregate is specified. The next example illustrates nested aggregates:

SELECT m.i, 
       SUM((SELECT SUM(n.j) 
               FROM tab2 WHERE j=15)) AS o 
    FROM tab m, tab2 n GROUP BY 1;

When the database server identifies the aggregate in the subquery above as a nested aggregate, it evaluates the inner SUM(n.j) aggregate in the subquery, if the outer aggregate is in the same query block where the table alias in the n.j column reference was declared.

A correlated aggregate in a subquery can include multiple correlated references to columns in the parent query block, as in the following example:

SELECT A.tabid, 
       (SELECT SUM(B.collength * A.rowsize) 
            FROM syscolumns B WHERE B.tabid = A.tabid)
    FROM systables A WHERE A.tabid = 1;

During execution of the statement above, the correlated aggregate

    SUM(B.collength * A.rowsize)

is evaluated in the subquery.

In all other cases, the database server treats an aggregate operating on a column of a table in a parent query block as a correlated aggregate.

Important: This behavior for evaluating correlated aggregate expressions was not fully supported in Informix 12.10.xC4 or 11.70.xC8, nor in earlier release versions.