You'll have to group by lessontitle too.. any time you use an aggregate function you have to group by whatever else is in your select statement.
On Wed, Feb 27, 2008 at 3:24 PM, John Ahlen <[EMAIL PROTECTED]> wrote: > Hello All, > > I've been trying to write the following query: > > <cfquery name="qGetKinderSc" datasource="#APPLICATION.datasource#"> > SELECT l.LessonTitle, l.lessonPK, > AVG(r.Rating) AS RatingAvg > FROM Rating r RIGHT OUTER JOIN LessonPlan l ON l.LessonPk = > r.LessonPk > WHERE l.Grade = 1 > AND l.Content = 2 > AND l.Approved > 0 > GROUP BY l.LessonPk > ORDER BY l.lessonTitle > </cfquery> > > However, debugging results in "You tried to execute a query that does not > include the specified expression 'LessonTitle' as part of an aggregate > function." > > The idea here is to connect a single lesson title in one table to it's > ratings in another. I need to use the RIGHT OUTER JOIN because not all > lessons have been rated and so do not have data in the Ratings table. > > Any suggestions as to what's wrong? > > Thanks very much in advance. > > John > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:3356 Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
