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 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.

Why won’t Oracle SQL let us use aliases in conditions?

Oracle PL/SQL won’t let users to use aliases in conditions. In most cases it’s very practical for me to use aliases instead of long statements. What’s the reason for that? What bad would happen if we

Oracle SQL use of subquery simulateneously in group by & select clauses in conjunction with CASE operator

Long title and strange problem: I want to use the with-statement in oracle SQL to reuse a sub-query as well in the select as group by clause. Additionally, I use a case statement in order to create mo

Why are table aliases not compiled out of existence when sharing SQL statements (on Oracle DBMS)

Quest Software/Knowledge Xpert states: If two identical SQL statements vary because an identical table has two different aliases, then the SQL is different and will not be shared. What sense does this

Why Trigger collection doesnt allow Data Triggers

Why Trigger collection doesnt allow the Data Triggers and other Triggers except the Event trigger? <StackPanel Orientation=Horizontal> <Label Name=LblHeader Margin=10,0,10,0 Content=Te

Why CAN I use aliases in the WHERE clause? (Oracle XE 11g)

While studying for the 1Z0-051 exam, I’ve read in many places that column aliases cannot be used in the WHERE clause. However, when I submit the following query: SELECT e.first_name, d.department_name

CakePHP – Column aliases in SQL queries

Is there a good way to specify column aliases when performing find() operations on a model? $this->User->find(‘first’, array( ‘fields’ => array( ‘CONCAT(firstname, ‘ ‘, surname) AS fullname’,

Allow only adding of column/table/view in Oracle SQL

Suppose I have a platform which allows users to enter some SQL queries and execute them. DB is Oracle 11.2 Is there any way to limit users’ operations to CREATE TABLE, CREATE VIEW and ALTER table ADD

Column aliases reference

This query breaks the number into its respected place like thousands, hundreds, fifties, etc. The problem is that I am unable to refer the column by its aliases name. In Oracle, I am getting this erro

Double Quotes in Oracle Column Aliases

Ok, this is bit of an obscure question, but hopefully someone can help me out with it. The system I’m working on builds a dynamic SQL string for execution inside a stored procedure, and part of that d

Why oracle package does not allow tables or views declaration

Inside an oracle package, we are allowed to declare functions and procedures to logically group them. Now why a package does not allow tables and views but only functions and procedures? Or more impor

Answers

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).

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

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)

While it seems a logical answer, in fact it’s a very user-unfriendly one. Prior to processing the query Oracle reads it, and by reading it the preprocessor can replace the alias by the original statement and still send the correct query to the database. same as you can code order by 1,2,3, you should also be able to group by 1,2,3 or alias.

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.