I can't be sure but I imagine the "distinct" clause is causing trouble here.
Have you tried removing it? Stacey > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Monday, 21 July 2003 16:34 > To: Multiple recipients of list delphi > Subject: [DUG]: Impending madness -- Interbase SUM > > > 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/ > --------------------------------------------------------------------------- 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/
