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

by

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

More Related Contents:

  1. GROUP BY with MAX(DATE) [duplicate]
  2. SQL – using alias in Group By
  3. Using an Alias in a WHERE clause
  4. SQL not a single-group group function
  5. ORA-00972 identifier is too long alias column name
  6. Using Alias In When Portion of a Case Statement in Oracle SQL
  7. Select First Row of Every Group in sql [duplicate]
  8. Oracle: how to UPSERT (update or insert into a table?)
  9. Get top results for each group (in Oracle)
  10. Comparing Dates in Oracle SQL
  11. must appear in the GROUP BY clause or be used in an aggregate function
  12. Oracle SQL query: Retrieve latest values per group based on time [duplicate]
  13. GROUP BY / aggregate function confusion in SQL
  14. Oracle “(+)” Operator
  15. How do you create a temporary table in an Oracle database?
  16. What exactly do quotation marks around the table name do?
  17. JOIN two SELECT statement results
  18. How can I get column names from a table in Oracle?
  19. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  20. How to check the maximum number of allowed connections to an Oracle database?
  21. Select without a FROM clause in Oracle
  22. Is there any way to flush output from PL/SQL in Oracle?
  23. SELECT from table with Varying IN list in WHERE clause
  24. Does Oracle use short-circuit evaluation?
  25. Advice Using Pivot Table in Oracle
  26. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  27. Local Temporary table in Oracle 10 (for the scope of Stored Procedure)
  28. Oracle Date TO_CHAR(‘Month DD, YYYY’) has extra spaces in it
  29. Backup SQL Schema Only?
  30. IN vs OR of Oracle, which faster?

Leave a Comment