The query was only an example to demonstrate the problem. The actual
query is quite different.
From what I infer, a nested aggregation isn't possible as there is no
way to define sub-groupings under the original GROUP BY.
In the given example, the subSELECT should be evaluated for every row,
and be oblivious to the grouping.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling
Tysvaer
Sent: Thursday, February 22, 2007 4:11 AM
To: Firebird-support
Subject: Re: [firebird-support] Nested aggregate functions

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


------------------------ Yahoo! Groups Sponsor --------------------~-->
See what's inside the new Yahoo! Groups email.
http://us.click.yahoo.com/0It09A/bOaOAA/yQLSAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups - Join or create groups, clubs, forums & communities.
Links





Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.