Shouldn't it be? SELECT avg(da.connects+ sum(ad.acd)) as averageTotalCalls
Regards, Andrew Scott http://www.andyscott.id.au/ > -----Original Message----- > From: Torrent Girl [mailto:moniqueb...@gmail.com] > Sent: Thursday, 10 March 2011 1:00 PM > To: cf-talk > Subject: using sum with average with mySQL > > > Hi All > > I have the following function that I am TRYING to get the average of a sum of > 2 added values. > > <cffunction name="getAverageTotalCalls" access="public"> > <cfargument name="employeeID" type="numeric" > required="yes"> > <cfargument name="dateWorked" type="string" required="yes"> > > <cfquery name="getAverageTotalCalls" datasource="XXXX"> > SELECT avg(da.connects)+avg(sum(ad.acd)) as averageTotalCalls > FROM dialeragent da, agentDaily ad > WHERE da.employeeID = <cfqueryparam > value="#arguments.employeeID#" cfsqltype="CF_SQL_NUMERIC" /> > > </cfquery> > <cfreturn getAverageTotalCalls> > </cffunction> > > So basically, I need to get the average of "connects" (only one record), get > the average of "acd" (can be multiple records so this needs to be summed > first, then averaged) THEN I need to add these two values. > > Here is the error I am getting: > > Error Executing Database Query. > Invalid use of group function > > > Any suggestions? > > TIA > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~~| > Order the Adobe Coldfusion Anthology now! > http://www.amazon.com/Adobe-Coldfusion- > Anthology/dp/1430272155/?tag=houseoffusion > Archive: http://www.houseoffusion.com/groups/cf- > talk/message.cfm/messageid:342872 > Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm > Unsubscribe: http://www.houseoffusion.com/groups/cf- > talk/unsubscribe.cfm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342874 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
