04-04-2024
13 november 2023
When you're grouping by a calculated column, you often find yourself copying the entire calculation into the group by clause.
For example:
select e.sal + nvl( e.comm, 0 ) as total
, count( * ) as thecount
from scott.emp e
where e.deptno = 20
group by e.sal + nvl( e.comm, 0 )
/
Oracle Database 23c now gives you the option to group by the alias. So instead of copying the entire calculation to the group by clause, you can now simply use the alias:
select e.sal + nvl( e.comm, 0 ) as total
, count( * ) as thecount
from scott.emp e
where e.deptno = 20
group by total
/
This feature is especially useful during script development, especially when the calculation undergoes frequent changes. Now, you don’t need to keep the column and the group by clause in sync.
Like with the order by clause, you can not only group by alias, but also by column position. However, this doesn’t work ‘out of the box’; you (or your DBA) have to enable this.
select e.sal + nvl( e.comm, 0 ) as total
, count( * ) as thecount
from scott.emp e
where e.deptno = 20
group by 1
/
ORA-00979: "E"."SAL": must appear in the GROUP BY clause or be used in an aggregate function
To enable grouping by column position, you have to set the parameter GROUP_BY_POSITION_ENABLED to true:
alter session set GROUP_BY_POSITION_ENABLED = true
/
select e.sal + nvl( e.comm, 0 ) as total
, count( * ) as thecount
from scott.emp e
where e.deptno = 20
group by 1
/
Be aware that when you enable this parameter, you check your existing queries thoroughly, because they might give you unexpected results. That’s why this feature is not enabled by default.
The source code used in this article can be found at:
https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/groupbyalias.sql
Also read my previous blog: Developer role.
Auteur: Patrick Barel
