>>Maybe I was not clear as I should be but here is an example in Firebird:
>>
>>select something,anotherthing,(select sum(thirdthing) from second b where b.something = a.something) total from a where
>>(select sum(thirdthing) from second b where b.something = a.something) > 10
> 
>tray sentence:
>select a.someting, a.anotherthing, b.total
>from first a inner join (
>select someting,sum(thirdhing) as total
>from second group by someting ) b on a.someting=b.someting
>where b.total>10

Other alternatives (though I guess whether or not the performance is acceptable will depend on table content):

a)
select something, anotherthing,
(select sum(thirdthing) from second b where b.something = a.something having sum(thirdthing) > 10) total
from a

b)
with tmp(something, total) as
(select something, sum(thirdthing)
from b
group by 1
having sum(thirdthing) > 10)
select a.someting, a.anotherthing, b.total
from a
join tmp on a.something = tmp.something

c)
execute block returns(something integer, anotherthing varchar(5) collate <whatever>, Total integer) as
begin
for select a.something, a.anotherthing, sum(b.thirdthing)
from a
join b on a.something = b.something
group by 1, 2
into :Something, :AnotherThing, :Total do
begin
if (Total > 10) then Suspend;
end
end

d)
select a.something, a.anotherthing, sum(b.thirdthing) Total
from a
join b on a.something = b.something
group by 1, 2
having sum(b.thirdthing) > 10


The result of a) differs from the others in that it will return a line (with Total = NULL if sum < 10) for all rows of a. b) should return identical results to your original query, whereas I think c) and d) will double the result of Total if there are two occurrences of a.something (triple if three, quadrouple if four etc).

>in Oracle it looks like
>
>select something,anotherthing,(select sum(thirdthing) from second b where b.something = a.something) total from a where
>total> 10
>
>and subquery executed only once but in Firebird subquery is executed twice. When you have very complex subquery and possible two or three times
>using total value in where clause it makes great impact on perfomance and of course it is very bad query but in Oracle it works like charm.

Don't know Oracle, but I admit that it is sometimes beneficial to tweak the SQL statement in Firebird to improve performance. I neither know whether or not it would feasible or if it would improve performance to allow the syntax you suggest, I think Dmitry (or similar) once wrote on this list that whilst being duplicate in terms of syntax and plan, it added almost nothing to the execution time since things would already be in the cache.

As for the syntax, I'm not used to the result of a subselect being available in the where clause (it would take me a bit of time to get accustomed to results of subselects being available in the where clause, but not the result of aggregate functions not in a subselect), but I may agree that it could make large statements more easily readable.

HTH,
Set