royteusink Posted October 27, 2006 Share Posted October 27, 2006 Hi all,I've got a small Access SQL question. I need to order my output by the AVG(SUM of multiple fields)Something like this, but this doesn't work: SELECT * FROM Reportings WHERE Year = '2006' ORDER BY AVG(WorkspecPoint+ToolboxPoint+NearmissPoint) DESC Does anyone of you guys have any idea how to solve this? Much Grasias,Roy Teusink Link to comment Share on other sites More sharing options...
aspnetguy Posted October 27, 2006 Share Posted October 27, 2006 try this, it appears that what you are trying is taking ht eaverage of those columns for the entire table not on a row by row basis. If you want row by row averages try this:select (col1+col2+col3)/3 from table;If you wnat to average several columns over the whole table this seems towork:select avg(col1+col2+col3) from table;so giving it another go this should work.SELECT * FROM Reportings WHERE Year = '2006' ORDER BY (WorkspecPoint+ToolboxPoint+NearmissPoint)/3 DESC providing all 3 columns are numeric Link to comment Share on other sites More sharing options...
aalbetski Posted October 27, 2006 Share Posted October 27, 2006 try this: SELECT AVG(WorkspecPoint + ToolboxPoint + NearmissPoint) FROM Reportings WHERE Year = '2006' ORDER BY AVG(WorkspecPoint + ToolboxPoint + NearmissPoint) DESC Remember that when you use aggregate functions, you cannot use select *, you need to do a group by for each field that is not using an aggregate function, ex: SELECT AVG(WorkspecPoint + ToolboxPoint + NearmissPoint) ,YearFROM Reportings GROUP BY YearORDER BY Year DESC, AVG(WorkspecPoint + ToolboxPoint + NearmissPoint) Link to comment Share on other sites More sharing options...
royteusink Posted October 27, 2006 Author Share Posted October 27, 2006 SELECT * FROM Reportings WHERE Year = '2006' ORDER BY (WorkspecPoint+ToolboxPoint+NearmissPoint)/3 DESCThis works good, thanks for the replies Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now