Oracle 23c New Feature — Grouping by an alias

Probably , this is my shortest blog post till now. In Oracle 23c , we can group by the alias name . This simple enhancement improves readability in the SQL statements.

SQL> select banner from v$version ;

BANNER           
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release    

SQL> create table t as select * from all_objects;
Table T created.

SQL> select to_char(created,'MON') as created_month , count(*) from t
  2* group by created_month;

CREATED_MONTH       COUNT(*) 
________________ ___________ 
MAR                    57620 
APR                     1014 
MAY                        1 

Maria DB

This feature has been in MySQL / MariaDB for quite sometime. Here is an example from MariaDB.

MariaDB [(none)]> create database testdb ;
Query OK, 1 row affected (0.008 sec)

MariaDB [(none)]> use testdb;
Database changed

MariaDB [testdb]> Create table t as select * from information_schema.tables;
Query OK, 324 rows affected (0.129 sec)
Records: 324  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select
    ->  to_char(create_time,
    ->  'MON') as created_month ,
    ->  count(*)
    -> from
    ->  t
    -> group by
    ->  created_month;

+---------------+----------+
| created_month | count(*) |
+---------------+----------+
| NULL          |      184 |
| May           |      140 |
+---------------+----------+

2 rows in set (0.007 sec)

Originally published at https://zahirmohideen.blogspot.com/2024/04/oracle-23c-new-feature-grouping-by-alias.html