Cannot use order by in this select?

Question

I have this query..

SELECT ClassId, Sum(TeachersCount) as NumCount
FROM ClassSubject 
GROUP BY ClassId 
ORDER BY NumCount

but when I run this, access pops up a box asking me the value of NumCount? But this is not a parameter, this is.. well this is the sum of teachers that can teach that class, and it is to be calculated. So why is access asking me for its (NumCount's) value?

What I want is to count the number of teacher in a class and order this by increasing value of teacher count, I think that's what my query does, but why is it asking me for value of NumCount? One more thing, if I remove this ORDER BY clause, it runs fine, without asking me the value of NumCount? So what's the problem?

Answer 1

You need:

SELECT ClassId, Sum(TeachersCount) as NumCount 
FROM ClassSubject 
GROUP BY ClassId 
ORDER BY Sum(TeachersCount)

You can also ORDER BY the ordinal number, in this case 2:

ORDER BY 2