It works fine on MSSQl thou I would been quite surprised if the DBISAM parser would handle it
Could always use a stored poc (oops sorry you wont have those either) Or why not Select SUM(Volume * Price), SUM(Volume) and a local calculated field HTH Neven ----- Original Message ----- From: "Mark Howard" <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Friday, October 31, 2003 1:28 PM Subject: Re: [DUG]: Weighted average in SQL > 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/ > > > --------------------------------------------------------------------------- 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/
