Monday, April 29, 2024

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)


 

No comments:

Post a Comment