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' ;

aa2In 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;

aa3And 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;

aa5The 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 ;

aa1