|Subject||Re: [firebird-support] SQL Optimation best way|
>A.field1 and field2 are the intern project number, b.fielda andb.fieldb are the extern ordernumber year and number.
>Sum(A.field3) where a.field1 = condition1 and a.field2 = condition2These seems like two completely different queries with no correlation,
>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?
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
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.