SQL Server "Invalid column name" error | Newbie

Question

I'm busy teaching myself SQL Server, using SQL Server Management Studio. Here's the code producing the error:

SELECT SalesPersonID, COUNT(SalesOrderID), YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
GROUP BY SalesYear;

Why does it throw this error?

Invalid column name 'SalesYear'.

Answer 1

Invalid column name 'SalesYear'.

This column will not be there in table SalesOrderHeader

SELECT SalesPersonID, COUNT(SalesOrderID), YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate)

Answer 2

This has to do with logical query processing model..Sales year is called an alias here and as per logical query processing model,below are the operators that are executed in sequence..

1 FROM 
2 WHERE 
3 GROUP BY 
4 HAVING 
5 SELECT
    5.1 SELECT list
    5.2 DISTINCT
6 ORDER BY 
7 TOP / OFFSET-FETCH

so ,in above steps ,group by will be executed in 3rd stage and your select will be executed in 5th stage..

This means your alias(which is in select stage) ,will be visible to operators following select (after 5),but not before them..

I would suggest taking a book which teaches basics well and i found Itzik Ben-Gan books to be extremely helpfull T-SQL Fundamentals Third Edition