Try putting a.Amount in your group by clause. Everything in your select list should be in the group by OR should have an aggregate function applied to it.
Also, I noticed you are referencing an alias "AverageAward" in your order by. I'm pretty certain SQL Server doesn't let you do that, but you might be OK with MySQL. ~Brad -------- Original Message -------- Subject: Help requested - Syntax for GROUP BY with HAVING ... From: "BobSharp" <[email protected]> Date: Tue, March 24, 2009 4:56 pm To: cf-talk <[email protected]> http://ttcfm.open.ac.uk/~bs3578/test1/Week9_Act6.cfm There is a problem with this script ... any clues, or links to examples, would be appreciated. <CFquery name="DeptAverage" datasource="bs3578" > SELECT a.Amount AS aAmount, d.DeptName AS dName, AVG(a.Amount) AS AverageAward, SUM(a.Amount) AS DeptTotal FROM incentiveawards a INNER JOIN employeedirectory e ON (e.ID = a.RecipientIDfk) INNER JOIN departments d ON (d.DepartmentID = e.DepartmentIDfk) WHERE SUM(a.Amount) > 5000 GROUP BY d.DeptName HAVING AVG(a.Amount) > 3000 ORDER BY AverageAward DESC; </CFquery> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320879 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
