I don't see how that SQL would work. You are using deptName in your select list and order by, but you aren't grouping by it. Unless MySQL allows you to do stuff that SQL Server doesn't, that should throw an error. Also, I had originally suggested placing the amount in the group by, but I hadn't noticed that you were getting the avg and sum of the amount column as well so you probably don't want to be grouping by amount. To use an aggregate function on the same column you are primarily grouping by would be useless, since the final result will return one row for every distinct value of the amount column. I'm not sure what the purpose of this query is, but if you are expecting to get one record back for each unique department with totals and average amounts for that department I think you would want to group by deptName and take amount out of the select list and the group by.
~Brad ----- Original Message ----- From: "BobSharp" <[email protected]> > <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) > GROUP BY a.Amount > HAVING SUM(a.Amount) > 5000 AND AVG(a.Amount) > 3000 > ORDER BY d.DeptName 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:320905 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
