SQL - using alias in Group By

sql group-by alias
Haoest picture Haoest · Oct 1, 2010 · Viewed 169.3k times · Source

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

Codo picture Codo · Oct 1, 2010

SQL is implemented as if a query was 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

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

So in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.

Related questions

Why doesn't Oracle SQL allow us to use column aliases in GROUP BY clauses?

This is a situation I'm generally facing while writing SQL queries. I think that writing the whole column (e.g. long case expressions, sum functions with long parameters) instead of aliases in GROUP BY expressions makes the query longer and …

Read More
Using group by on multiple columns

I understand the point of GROUP BY x. But how does GROUP BY x, y work, and what does it mean?

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More