GROUP BY on Alias

Aliases can sometimes come in handy. The most obvious example is when you want to give your columns more descriptive names. This is all fine but aliases have some limitations also, one limitation is that you can’t do group by on an alias. This came to my attention the other day when a customer came to me and asked what was wrong with his tsql statement. He wrote something like: SELECT XYZ AS [Alias], COUNT(*) FROM TABLE GROUP BY [Alias]. SQL Server immediately responded with error: Invalid column name ‘Alias’

Now why is this? Well it’s really not so strange if you know the logical order that the engine works. The actual SELECT statement with the alias is applied AFTER the JOIN, UNION, WHERE, HAVING and GROUP BY operators. The only time you can use an alias is with ORDER BY which is applied last in the query processing sequence, even after SELECT. So by the time you try to group by an alias SQL Server has no clue what the alias is.