OK, think I've got it now ... GROUP BY contains the main criteria for the query (not to be confused with ORDER BY) HAVING should contain any (both) conditions needed for the search.
http://ttcfm.open.ac.uk/~bs3578/test1/Week9_Act6.cfm thanks BTW are there any tutorials online that cover this kind of CFquery ? ----- Original Message ----- From: <[email protected]> To: "cf-talk" <[email protected]> Sent: Tuesday, March 24, 2009 10:19 PM Subject: RE: Help requested - Syntax for GROUP BY with HAVING ... > > 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:320887 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
