SQL Server Error "Invalid column name" | Newbie

Question

I am busy training SQL Server using SQL Server Management Studio. Here is the code generating the error:

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

Why does this cause this error?

Invalid column name 'SalesYear'.

Answer 1

Invalid column name 'SalesYear'.

This column will not be in the table SalesOrderHeader

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

Answer 2

This is due to the logical query processing model . Sales year is called an alias here and in accordance with the logical query processing model, the following are statements that are executed sequentially.

 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 

therefore, in the above steps, the group will be completed in the third step, and your choice will be made in the 5th step.

This means that your alias (which is at the selection stage) will be visible to operators after selection (after 5), but not earlier than them.

I would suggest taking a book that teaches you the basics well, and I found Itzik Ben-Gan books extremely useful T-SQL Fundamentals Third Edition

Answer 3

You can try the following query and test to get the expected result.

 DECLARE @SalesOrderHeader TABLE(SalesPersonID INT,SalesOrderID INT,OrderDate DATETIME) INSERT INTO @SalesOrderHeader SELECT 1,101,'20-Mar-2018' UNION ALL SELECT 1,102,'21-Mar-2018' UNION ALL SELECT 1,102,'21-Mar-2018' UNION ALL SELECT 2,202,'21-Mar-2018' SELECT * FROM @SalesOrderHeader SELECT SalesPersonID AS SalesPersonID, COUNT(SalesOrderID) NoOfOrder, YEAR(OrderDate) AS SalesYear FROM @SalesOrderHeader GROUP BY SalesPersonID,YEAR(OrderDate); 

Thanks.