> Am I trying the impossible?
>
> I created a minimal test case. We have 3 simple tables:
>
> create table t1
> ( a integer, b integer, c integer );
>
> create table t2
> ( a integer, b integer, c integer );
>
> create table t3
> ( b integer, d integer );
>
>
> And the following query:
>
> select y.D, sum(x.c),
> (select sum(z.C)
> from t2 z
> join t3 w on z.B = w.B
> where z.A = x.A)
> from t1 x
> join t3 y on x.B = y.B
> GROUP by y.D;
>
> ...gives me this error:
>
> Invalid expression in the select list (not contained in either an
> aggregate function or the GROUP BY clause)
>
>
> In both Firebird 1.5 and 2.0. The problem is the WHERE clause in
> sub-select, but I don't understand why. I mean, I think I know why,
> but I don't understand why this limitation exists.

In this query you must include "x.A" at "group by" list to make it
syntax correct. It can change results of course.

Also you may try to rewrite it as

select y.D, sum(x.c), sum(z.C)
from t1 x
join t3 y on x.B = y.B
left join (t2 z
join t3 w on z.B = w.B) on z.A = x.A

GROUP by y.D;

Regards,
Vlad