>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. HTH, Set ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
