Selecting correlated aggregates in subqueries

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

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;

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

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

    SUM(B.collength * A.rowsize)

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.