ORDER BY statement causing error

[dharkangel]

Hi everyone,

The following query works just fine until I add the ORDER BY statement at the end. Then I recieve a 'You tried to execute a query that does not include the specified expression 'DaysLate' as part of an aggregate function.' What am I doing wrong?

SELECT getBarcelonaBestPerformerData.[Assign To] AS Assigned_To, Count(getBarcelonaBestPerformerData.[Issue ID]) AS NumberOfActions, Round(Avg(getBarcelonaBestPerformerData.DaysToClose)) AS DaysToClose, Round(Avg(getBarcelonaBestPerformerData.DaysLate)) AS DaysLate"
WHERE getBarcelonaBestPerformerData.Component = '" & actionSet.Fields("Component") & "'"
GROUP BY getBarcelonaBestPerformerData.[Assign To] ORDER BY DaysLate;

 

[PHV]

Try this:
ORDER BY 4

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dharkangel]

PHV, I don't think you're human. It works, but why?

 

[Golom]

Because there only some circumstances where you can refer to a field alias in the Order By clause.

This isn't one of them.

You can however, refer to a field by it's ordinal position in the SELECT clause.

 

[dharkangel]

Thanks for the explanation.