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

Question

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 less readable. Why doesn't Oracle SQL allow us to use the column aliases in GROUP BY clause? There must be an important reason behind it.

Answer 1

It isn't just Oracle SQL, in fact I believe it is conforming to the ANSI SQL standard (though I don't have a reference for that). The reason is that the SELECT clause is logically processed after the GROUP BY clause, so at the time the GROUP BY is done the aliases don't yet exist.

Perhaps this somewhat ridiculous example helps clarify the issue and the ambiguity that SQL is avoiding:

SQL> select job as sal, sum(sal) as job   2  from scott.emp   3  group by job;  SAL              JOB --------- ---------- ANALYST         6000 CLERK           4150 MANAGER         8275 PRESIDENT       5000 SALESMAN        5600 

Answer 2

I know this is an old thread, but it seems the users problem wasn't really solved; the explanations were good in explaining why the group by clause doesn't let you use aliases, but no alternative was given.

Based on the info above, the aliases can't be used in the group by since group by runs first, before aliases from the select clause are stored in memory. So the simple solution which worked for my view was to add an outer select which simply selects the aliases, and then groups at that same level.

Example:

SELECT alias1, alias2, alias3, aliasN FROM (SELECT field1 as alias1, field2 as alias2, field3 as alias3, fieldN as aliasN  FROM tableName  WHERE ' ' = ' ') GROUP BY alias1, alias2, alias3, aliasN 

Pretty straight forward once you see the solution, but a PITA if trying to figure out by yourself for first time.

This is the only way I have been able to "group by" for a derived field from a case statement, so this is a good trick to know.