Subject Re: [firebird-support] SQL Optimation best way
Author setysvar
>A.field1 and field2 are the intern project number, b.fielda and
b.fieldb are the extern ordernumber year and number.
>Sum(A.field3) where a.field1 = condition1 and a.field2 = condition2
>In the same statement I would include
>Sum(b.fieldc) where b.fielda = a.field4 and b.fieldb = a.field5
>It is possible without cte?

These seems like two completely different queries with no correlation,
so my guess is that the answer is no, you need a cte or something
similar (e.g. execute block).

However, if A has a 1:many correlation to B, and you only want to count
those B's that also match condition1 and condition2, then something like

select sum(A.field3), sum((select sum(b.fieldc) from B where b.fielda =
a.field4 and b.fieldb = a.field5)) --you need double parenthesis and sum
from A
where a.field1 = :condition1 and a.field2 = :condition2

could be a solution (make sure to test so that you don't get double or
triple of what you want from sum(B) if some records of A had the same
value for field1, field2, field4 and field5).

I doubt this is a noticable optimization compared to cte or execute
block, I think of it as an alternative suitable in some cases, but I
neither expect it to be superior nor inferior in those cases.