Answer lies beneath the way of Query Processing

The Question I asked in my previous post is why we should not use column alias used in SELECT list in Group BY Clause and we used that in ORDER By Clause.

The answer lies in the way how a SELECT Statement is processed.

SQL is differ from other programming language in an aspect how it is processing the Code. Normally the a code in a programming language is processed top down method, meaning, first written statement will be processed first. But SQL wont.

A 'SELECT' Statement is processed by SQL Server as follows.

FROM Clause is processed first.
On condition of Join Clause is Processed Second.
Join condition is Processed third.
Where is Processed fourth.
Group By is Processed fifth.
With {Cube} | {Rollup} is processed sixth.
Having is Processed seventh.
SELECT is processed eighth.
Distinct is processed 9th
ORDER By is processed 10th.
TOP Clause is processed 11th.

Suppose if you omitted any optional clauses that steps will be omitted in the processing.

Now look at the query.

Group By is Processed before the Select so we can not use the alias created in the SELECT in Group By.

And Order By is Processed after SELECT so we can use that in ORDER BY Clause.

For further reading about how queries are processed read Chapter 1 and 2 of the book.

Inside Microsoft® SQL Server™ 2005 T-SQL Querying

By Itzik Ben-Gan - (Solid Quality Learning), Lubor Kollar, Dejan Sarka