Wednesday, October 31, 2012

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

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
 
http://stackoverflow.com/questions/2681494/why-doesnt-oracle-sql-allow-us-to-use-column-aliases-in-group-by-clauses 

No comments:

Post a Comment