SELECT column alias can be used in ORDER BY but not in GROUP BY clause

We can use SELECT column alias in ORDER BY clause. But we can't use in GROUP BY clause.

SQL is implemented as if a query was executed in the following order:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

This order in most relational database systems explains which names (columns or aliases) are valid because they must have been introduced in a previous step. There are exceptions though.

You could always use a subquery so you can use the alias 

SQL Server doesn't allow you to reference the alias in the GROUP BY clause because of the logical order of processing. The GROUP BY clause is processed before the SELECT clause, so the alias is not known when the GROUP BY clause is evaluated. This also explains why you can use the alias in the ORDER BY clause. 

In Short,

ORDER BY clause is processed after the SELECT statement; 

GROUP BY clause is processed before the SELECT statement.

Difference Between GROUP BY and ORDER BY

Group By : It is a way to sub-total your results,or perform some other aggregate functions on them.  
Order By : It is a simply a way to sort your results. It does not affect what shows up in your result set,only what order it is displayed. 


ORDER BY alters the order in which items are returned.

GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc).

The difference is exactly what the name implies: a group by performs a grouping operation, and an order by sorts.
If you do "SELECT * FROM Customers ORDER BY Name" then you get the result list sorted by the customers name.
If you do "SELECT IsActive, COUNT(*) FROM Customers GROUP BY IsActive" you get a count of active and inactive customers. The group by aggregated the results based on the field you specified.


Moreover: if you GROUP, the results are not necessarily sorted; although in many cases they may come out in an intuitive order, that's not guaranteed by the GROUP clause. If you want your groups sorted, always use an explicity ORDER BY after the GROUP BY. – Dave Costa


Group by and order by are two different clause in SQL. One group the result set  and other (ORDER BY) order the result set.


Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.


Order of columns make no difference in the GROUP BY Clause. For Example 


GROUP BY A, B, C
and
GROUP BY C,A,B 
Both returns the same results.


Order of Columns makes difference in ORDER BY Clause.For Example


ORDER BY A, B, C
and
ORDER BY C,A,B 
Both returns the same number of Records. But Row order will be different.

GROUPBY, HAVING and ORDER BY Usage in SQL Server

I have often seen T-SQL beginners having confusion over the sequence and usage of GroupBy, Having and Order By clause. Here’s a simple example:

I am using the AdventureWorks database.

USE AdventureWorksGOSELECT CustomerID, SUM(TaxAmt) AS CustomerTaxFROM Sales.SalesOrderHeaderGROUP BY CustomerIDHAVING SUM(TaxAmt) > 2000ORDER BY CustomerID

As you can see, we are using HAVING to filter rows based on an aggregate expression. Note that you can include nonaggregate columns in the HAVING clause, however the condition is that these columns must appear in the GROUP BY clause.