John, Try putting your lessonTitle in the Group BY section
Jim -----Original Message----- From: John Ahlen [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 27, 2008 12:25 PM To: CF-Newbie Subject: Using SQL to return column average 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:3357 Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
