SQL - using alias in Group By

Question

Just curious about SQL syntax. So if I have

SELECT 

 itemName as ItemName,

 substring(itemName, 1,1) as FirstLetter,

 Count(itemName)

FROM table1

GROUP BY itemName, FirstLetter

This would be incorrect because

GROUP BY itemName, FirstLetter 

really should be

GROUP BY itemName, substring(itemName, 1,1)

But why can't we simply use the former for convenience?

Answer 1

In SQL, to fetch the resultset the queries will be executed in the following order:

1. FROM clause

2. WHERE clause

3. GROUP BY clause

4. HAVING clause

5. SELECT clause

6. ORDER BY clause

In most of the RDBMS, this order explains which names (columns or aliases) are valid because they must have been introduced in the previous step.

Therefore in SQL Server and Oracle, you cannot use the term in the GROUP BY clause that you have defined in the SELECT clause because the GROUP BY is executed before the SELECT clause.