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?
Tomalak :
select\n count(count_col),\n alias_column\nfrom\n (\n select \n count_col, \n (select value from....) as alias_column \n from \n table\n ) as inline\ngroup by \n alias_column\n\n\nGrouping 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.\n\nTo GROUP BY the result of a sub-query, you will have to take a little detour and use an nested query, as indicated above.",
2008-11-06T12:11:04
Tony Andrews :
Nest the query with the alias column:\n\nselect count(*), alias_column\nfrom\n( select empno, (select deptno from emp where emp.empno = e.empno) as alias_column\n from emp e\n)\ngroup by alias_column;\n",
2008-11-06T12:20:39
ian_scho :
select count(*), (select * from....) as alias_column \nfrom table \ngroup by (select * from....)\n\n\nIn Oracle you cannot use an alias in a group by clause.",
2008-11-06T12:27:18
Andrew :
To use an alias in Oracle you need to ensure that the alias has been defined by your query at the point at which the alias is being used.\n\nThe most straightforward way to do this is to simply treat the original query as a subquery -- in this case, \n\nselect count(*), (select * from....) as alias_column \nfrom table \ngroup by (select * from....)\n\n\nbecomes\n\nselect count, alias_column \nfrom\n (select count(*) as count, (select * from....) as alias_column \n from table)\ngroup by alias_column \n\n\nI can't speak to the performance implications, but it's very quick to write if you're trying to re-use an alias in your query - throw everything in parentheses and jump up a level...",
2011-04-19T18:12:09
elfcheg :
Since Oracle 23c we have GROUP BY column alias in Oracle! And you can also use alias in HAVING clause.\nAnd instead of using column alias you can now use column position as in ORDER BY. But only after explicitly enabling with\nalter session set group_by_position_enabled = true;\n\nYou may check Connor's video with a bit more detailed explanation with examples: youtube.com/watch?v=dcMBwVwjZGE",
2023-04-15T14:58:17