Subject | Re: [firebird-support] Nested aggregate functions |
---|---|

Author | Svein Erling Tysvaer |

Post date | 2007-02-22T09:10:52Z |

Rick Debay wrote:

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

> I think the test for nested aggregate functions is incorrect.Hi Rick, I'm answering since no-one else have done so.

> 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.

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