Rick Debay wrote:

> I think the test for nested aggregate functions is incorrect.
> The following statement is allowed:
>
> SELECT
> SUM(t1.COL1 - (SELECT t2.COL1 FROM TABLE2 t2 WHERE t2.COL2 = ?))
> FROM
> TABLE1 t1
>
> This statement is not (returns 'Nested aggregate functions are not
> allowed'):
>
> SELECT
> SUM(t1.COL1 - (SELECT SUM(t2.COL1) FROM TABLE2 t2 WHERE t2.COL2 = ?))
> FROM
> TABLE1 t1
>
> In both cases the SELECT on TABLE2 is standalone, and does not require
> any nested aggregation on the original stream.

Hi Rick, I'm answering since no-one else have done so.

I've never tried doing anything like this, but to me it seems like a
nested aggregation since you want the sum of t2.COL1 to be subtracted
from every individual t1.COL1.

Suppose you had these values:

Table1
COL1
2
4

Table2 (ignoring COL2 for now)
COL1
1
3

Doing your calculation would mean

2-(1+3)+4-(1+3)

and I consider (1+3) to be a nested aggregation.

Moreover, I would expect your particular calculation to be equivalent to

SELECT
SUM(t1.COL1) -
(SELECT SUM(t2.COL1) FROM TABLE2 t2 WHERE t2.COL2 = ?) *
(SELECT COUNT(*) FROM TABLE1 t1a) //or simply COUNT(*)?
FROM
TABLE1 t1

to work (even though I haven't tried it).

Set