Using alias of column in SQL query of SQL Server 2012
The SQL query in SQL Server may have alias of column. Using this alias out of name of column in query is possible only in ORDER BY clause. In this post I show You why?
Begin with use AdventureWorks2012
database.
USE AdventureWorks2012;
In first query You select year of ModifiedDate
column from Person
table in Person
schema. This year must be bigger than 2002 so You should use WHERE
clause.
SELECT YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE year > '2002' ;
In example above You use year
alias for ModifiedDate
column. Using this alias in WHERE
clause is not possible. You get this message:
Msg 207, Level 16, State 1, Line 3 Invalid column name 'year'.
It is because WHERE
clause phase is execute before select data columns phase .
In example below using all expression of column from SELECT
statement instead of alias wil be correct.
SELECT YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE YEAR([ModifiedDate]) > '2002' ;
In another example we use GROUP BY
clause and HAVING
clause.
SELECT YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE YEAR([ModifiedDate]) > '2002' GROUP BY year HAVING COUNT([BusinessEntityID]) > 200;
In this example using year
alias call error. In GROUP BY
clause we may not use alias.
Msg 207, Level 16, State 1, Line 5 Invalid column name 'year'.
The next example is correct:
SELECT YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE YEAR([ModifiedDate]) > '2002' GROUP BY YEAR([ModifiedDate]) HAVING COUNT([BusinessEntityID]) > 200;
And third situation where we don’t use alias is alias for agregate function and use this alias in HAVING
clause.
SELECT YEAR([ModifiedDate]) as year, COUNT([BusinessEntityID]) as number FROM [AdventureWorks2012].[Person].[Person] WHERE YEAR([ModifiedDate]) > '2002' GROUP BY YEAR([ModifiedDate]) HAVING number > 1;
The example above call error message.
Msg 207, Level 16, State 1, Line 7 Invalid column name 'number'.
The correct is example below:
SELECT YEAR([ModifiedDate]) as year, COUNT([BusinessEntityID]) as number FROM [AdventureWorks2012].[Person].[Person] WHERE YEAR([ModifiedDate]) > '2002' GROUP BY YEAR([ModifiedDate]) HAVING COUNT([BusinessEntityID]) > 1;
The only case using alias of column is ORDER BY
clause, because this phase is calling after select statement.
SELECT YEAR([ModifiedDate]) as year, COUNT([BusinessEntityID]) as number FROM [AdventureWorks2012].[Person].[Person] WHERE YEAR([ModifiedDate]) > '2002' GROUP BY YEAR([ModifiedDate]) HAVING COUNT([BusinessEntityID]) > 1 ORDER BY year ;