Thursday, February 17, 2011

Group by alias (Oracle)

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?

From stackoverflow
  • 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.

  • It does not work, I get

    'subquery expression not allowed here'
    

    error message

    Tomalak : Please use the comment function to comment on an answer.
  • 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;
    
  • select count(*), (select * from....) as alias_column 
    from table 
    group by (select * from....)
    

    In Oracle you cannot use an alias in a group by clause.

0 comments:

Post a Comment