Small AVG Question

[royteusink]

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

[aspnetguy]

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

[aalbetski]

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)

[royteusink]

SELECT * FROM Reportings WHERE Year = '2006' ORDER BY (WorkspecPoint+ToolboxPoint+NearmissPoint)/3 DESC

This works good, thanks for the replies