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

Hi Mehper,

While I agree it would be helpful to reference expressions with aliases in the GROUP BY clause, my guess is that it is not possible because the GROUP BY clause is evaluated before the SELECT clause.

This would also explain why you can use column aliases in the ORDER BY clause (i-e: the ORDER BY clause is evaluated last).

Answer 2

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 3

But some RDBMS do, this works on PostgreSQL:

select emp.lastname || ' ' || emp.firstname as fullname, count(emp_work.*) as cnt
from emp
left join emp_work using(emp_id)
group by fullname

That will work, as long as the grouped alias is not the result of aggregate functions, group by cnt will not work

But I can hazard a guess that group by fullname gets expanded to group by emp.lastname || ' ' || emp.firstname as fullname, and the SELECT clause just pick the fullname result from that grouping; though syntactically it looks the other way around. GROUP always executes first, then projections last(i.e. SELECT)