Hi Neven No DBISAM does not appear to like the "Select (Select Sum(blah blah" contruct. It complains about finding Sum when right parenthesis was expected.
Actually even had this worked, I guess I would have had to include the same set of (fairly complex WHERE conditions for each of the internal SELECTS and the EXTERNAL one. All in all, probably easier to use a Temp table, but thanks for the suggestion. Mark On 31 Oct 2003 at 11:59, Neven MacEwan wrote: > Mark > > SELECT > (Select sum(Volume*Price) from Table) > / > (Select sum(Volume) from Table) > as AvgPrice? > > Neven > > ----- Original Message ----- > From: "Mark Howard" <[EMAIL PROTECTED]> > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> > Sent: Friday, October 31, 2003 11:45 AM > Subject: [DUG]: Weighted average in SQL > > > > Hi All > > > > Not sure if this problem is specific to DBISAM or not. > > > > I WANT to run some SQL that goes (greatly simplified): > > > > Select (SUM(Volume * Price) / SUM(Volume)) as AvgPrice > > > > to give a weighted average Price but am prevented from using an > > aggregate function within an expression. > > > > I know I can do this by reading the aggregates into a Temp table, > > but is > there a nifty > > alternative way to achieve this? > > > > TIA > > > > Mark > > -------------------------------------------------------------------- > > ------ > - > > 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/ > --------------------------------------------------------------------------- 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/
