23c New Feature – Alias in the GROUP BY Clause?

You read that right!

One of the best features of Oracle 23c is the use of aliases in the GROUP BY clause. It’s a game-changer for query writing! With aliases, you can simplify your select statements and avoid listing all the columns and expressions that aren’t part of aggregate functions in the GROUP BY clause. This makes your SQL statement shorter and more readable.

If you’re using plain column names in the select clause, you might not notice a big difference. But trust me, once you start using aliases in the GROUP BY clause, you’ll never want to go back, unless you are using an older release of the database.

Here are some basic example, where you might not see the value so much of this feature:

--pre 23c
select deptno d, count(*) number_of_employees
from emp
group by deptno;

--23c
select deptno d, count(*) number_of_employees
from emp
group by d;

However, if you are using expressions in the select clause, the difference is much noticeable. The expressions with DECODE or CASE functions, are very lengthy and can get complicated.
See an example below, how much easier the SQL statement is to read it and to understand it.

select case
        when  owner ='SYS' then 'SYS objects'
        else 'Non-SYS Objects'
      end as obj_type, count(*)
from dba_objects
group by obj_type;

Do you see the value of this feature? Have you used it already? Let me know in the comments section, I read all the feedback!