Peter AFAIK distinct and aggregates are mutally exclusive, I don't use distinct much as it it of limited use and can be replicated by group by,
so in 'vanilla' SQL try select O.INVNO, SUM(D.QTY) as Total from ORDER O, DETAIL D where O.INVNO=D.INVNO group by O.INVNO order by Total also you should prob use ANSI join clauses select O.INVNO, SUM(D.QTY) as Total from ORDER O join DETAIL D on O.INVNO=D.INVNO group by O.INVNO order by Total ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Monday, July 21, 2003 4:34 PM 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/
