Can I ORDER BY on a SUM column? 1

[elvenmaiden]

This is what I have, but it doesn't seem to recognize the AcceptanceRate as a valid sort field.

SELECT qryStats.Name, qryStats.Subject, qryStats.Questions, qryStats.Solutions, AVG(qryStats.AcceptRate)as AcceptanceRate FROM qryStats WHERE qryStats.UserEmail = '&quot; + mstrUser + &quot;' AND qryStats.OrigDate >= # &quot; & DTPBegin.Value & &quot; # AND qryStats.OrigDate <= # &quot; & DTPEnd.Value & &quot; # GROUP BY qryStats.Name, qryStats.Subject, qryStats.Questions, qryStats.Solutions ORDER BY AcceptanceRate

 

[rac2]

In some clauses column aliases are not allowed. Don't know if this is standard SQL or particular implementations. But the expression can be used in the ORDER BY clause, so use

. . .
ORDER BY AVG(qryStats.AcceptRate)

 

[karluk]

Another possibility is

order by 5

As far as I know all commonly used databases allow you to specify your order by clause based on position within the select. In this case AVG(qryStats.AcceptRate) is the fifth column in your result set.

 

[adam0101]

The column alias AcceptanceRate can also be used. Column aliases cannot be used anywhere else though.