Hi folks, Can anyone spot why this dirt-simple Interbase SQL is rejected with an invalid column reference UNLESS I remove all of the SUM, GROUP and ORDER clauses:
select distinct O.INVNO, SUM(D.QTY) as Total from ORDER O, DETAIL D where O.INVNO=D.INVNO group by O.INVNO order by Total All I'm trying to do is return a result set which contains relevant fields from a master table, plus a Total field obtained by summing matching rows of a detail table. Bread and butter SQL, but today I'm missing something very obvious. NB: This is cut down and edited from a far more complex and less readable query (originally using join as it should properly do, but I've been all over the park since then without success). I can get one just like it to working for me against, say, Paradox but Interbase just won't buy it. TIA! cheers, peter =========================================== Peter Hyde, Development Director, SPIS Ltd, Christchurch, New Zealand * TCompress/TCompLHA component sets for Delphi/Kylix/C++ * TurboNote+: http://TurboNote.com -- top-rated onscreen sticky notes Find all the above and MORE at http://spis.co.nz --------------------------------------------------------------------------- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi" Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
