Group by alias (Oracle)

Question

How to 'group by' a query using an alias, for example:

select count(*), (select * from....) as alias_column  from table  group by alias_column 

I get 'alias_column' : INVALID_IDENTIFIER error message. Why? How to group this query?

Answer 1

select   count(count_col),   alias_column from   (   select      count_col,      (select value from....) as alias_column    from      table   ) as inline group by    alias_column 

Grouping normally works if you repeat the respective expression in the GROUP BY clause. Just mentioning an alias is not possible, because the SELECT step is the last step to happen the execution of a query, grouping happens earlier, when alias names are not yet defined.

To GROUP BY the result of a sub-query, you will have to take a little detour and use an nested query, as indicated above.

Answer 2

Nest the query with the alias column:

select count(*), alias_column from ( select empno, (select deptno from emp where emp.empno = e.empno) as alias_column   from emp e ) group by alias_column;