Group by 'Column Position' in Oracle 23ai

In this blog post ( https://zahirmohideen.blogspot.com/2024/04/oracle-23c-new-feature-grouping-by-alias.html ), we were introduced to a minor enhancement to the group by clause. 

We can also use the column position in the group by clause in Oracle 23ai, if the parameter (group_by_position_enabled) is set to TRUE. 

From my perspective, this shortcut makes the code short, but it may not be readable or ideal in the long term. 

Again, this is my view. 

Here is an example.

SQL> create table t as select object_name , object_type from all_objects;

Table T created.

SQL> sho parameter group_by_po

NAME                      TYPE    VALUE 

------------------------- ------- ----- 

group_by_position_enabled boolean FALSE 

SQL> select object_type , count(*) as cnt from t group by  1  having cnt > 1;

Error starting at line : 1 in command -

select object_type , count(*) as cnt from t group by  1  having cnt > 1

Error at Command Line : 1 Column : 8

Error report -

SQL Error: ORA-03162: "OBJECT_TYPE": must appear in the GROUP BY clause or be used in an aggregate function as 'group_by_position_enabled' is FALSE

Help: https://docs.oracle.com/error-help/db/ora-03162/

More Details :

https://docs.oracle.com/error-help/db/ora-03162/

SQL> alter session set group_by_position_enabled = true;

Session altered.

SQL> select object_type , count(*) as cnt from t group by  1  having cnt > 1;

OBJECT_TYPE                CNT 

_____________________ ________ 

TABLE                     2510 

CLUSTER                     10 

INDEX                     2486 

SYNONYM                  13105 

TABLE PARTITION            656 

INDEX PARTITION            608 

SEQUENCE                    37 

DIRECTORY                   12 

PACKAGE                    701 

VIEW                      8446 

FUNCTION                   307 

PROCEDURE                   54 

TYPE                      1656 

CONTEXT                     23 

OPERATOR                    60 

DOMAIN                     109 

LOB                          3 

TABLE SUBPARTITION          33 

LOCKDOWN PROFILE             3 

PACKAGE BODY               165 

CONSUMER GROUP              18 

JOB CLASS                    3 

DESTINATION                  2 

SCHEDULE                     4 

WINDOW                       9 

SCHEDULER GROUP              4 

EVALUATION CONTEXT           2 

XML SCHEMA                  19 

INDEXTYPE                   11 

TYPE BODY                   81 

RULE SET                     2 

TRIGGER                    129 

JAVA CLASS               32754 

JAVA RESOURCE             1228 

JAVA DATA                   17 

LIBRARY                     25 

36 rows selected. 

Let me know your comments, please!

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)

 

GROUP BY behavior in MySQL / MariaDB

It is better to unlearn and relearn the SQL , if we need to work on different DBMS. I was surprised by the "GROUP BY" behavior in MySQL / MariaDB. 

Typically , when we are using GROUP BY , all the columns except the aggregated  columns mentioned in the SELECT clause has to be defined in the GROUP BY clause. If any columns are missed , then the prompt produces an error .  However , in MySQL / MariaDB , this produces the result ( sum / avg ) . This may be a side effect ,which may not be the desirable result , we are looking for. 

Let us create a simple table in Oracle , MS SQL Server , PostgreSQL , MySQL and MariaDB.When I issue the GROUP BY without mentioning other columns , all the DBMSes except MariaDB and MySQL produced an error. 

Here is an example 

ORACLE 

SQL> Create table emp_hours
  2  ( empid int ,
  3    workweek int ,
  4    hrstype varchar(10) ,
  5    hrs number(5,2)
  6    ) ;

Table created.

SQL> insert into    emp_hours values  ( 1 , 20 , 'REG' , 20 ) ;
1 row created.

SQL> insert into    emp_hours values( 1 , 20 , 'OT' , 4.5 ) ;
1 row created.

SQL> insert into    emp_hours values ( 2 , 20 , 'REG' , 40 ) ;
1 row created.

SQL> insert into    emp_hours values( 2 , 20 , 'REG' , 35 ) ;
1 row created.

SQL> insert into    emp_hours values ( 3 , 20 , 'OT' , 4.5 ) ;
1 row created.

SQL> commit;
Commit complete.

SQL> select empid , hrstype , sum(hrs) from emp_hours;
select empid , hrstype , sum(hrs) from emp_hours
       *
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> select empid , hrstype , sum(hrs) from emp_hours group by empid , hrstype ;

     EMPID HRSTYPE      SUM(HRS)
---------- ---------- ----------
         3 OT                4.5
         1 REG                20
         2 REG                75
         1 OT                4.5

MS SQL Server


1> Create table emp_hours
2> ( empid int ,
3>   workweek int ,
4>   hrstype varchar(10) ,
5>   hrs numeric(5,2)
6>   ) ;
7> go
1> insert into    emp_hours values  ( 1 , 20 , 'REG' , 20 ) ;
2> insert into    emp_hours values( 1 , 20 , 'OT' , 4.5 ) ;
3> insert into    emp_hours values ( 2 , 20 , 'REG' , 40 ) ;
4> insert into    emp_hours values( 2 , 20 , 'REG' , 35 ) ;
5> insert into    emp_hours values ( 3 , 20 , 'OT' , 4.5 ) ;
6> go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)  

1> select empid , hrstype , sum(hrs) from emp_hours;
2> go
Msg 8120, Level 16, State 1, Server AUSTIN02, Line 1
Column 'emp_hours.empid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

1> select empid , hrstype , sum(hrs) from emp_hours group by empid , hrstype ;
2> go
empid hrstype
----- ------- -
1 OT 4.50
3 OT 4.50
1 REG 20.00
2 REG 75.00

(4 rows affected)       

PostgreSQL 

Similarly , we get the error as shown below in Postgres for the above test case.

ERROR: column "emp_hours.empid" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803

Character: 8

MySQL 

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.5.49-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.00 sec)



mysql> select empid , hrstype , sum(hrs) from emp_hours;
+-------+---------+----------+
| empid | hrstype | sum(hrs) |
+-------+---------+----------+
|     1 | REG     |   104.00 |
+-------+---------+----------+
1 row in set (0.00 sec)

Instead of a error , the above SQL summed up the hours and produced one record.  

Personally , I would prefer the above SQL to error out , instead of giving this answer. 

mysql> select empid , hrstype , sum(hrs) from emp_hours group by empid , hrstype ;
+-------+---------+----------+
| empid | hrstype | sum(hrs) |
+-------+---------+----------+
|     1 | OT      |     4.50 |
|     1 | REG     |    20.00 |
|     2 | REG     |    75.00 |
|     3 | OT      |     4.50 |
+-------+---------+----------+
4 rows in set (0.05 sec)

MariaDB

mysql> select version();
+--------------------------------------------+
| version()                                  |
+--------------------------------------------+
| 10.3.11-MariaDB-1:10.3.11+maria~bionic-log |
+--------------------------------------------+
1 row in set (0.00 sec)


mysql> select empid , hrstype , sum(hrs) from emp_hours;
+-------+---------+----------+
| empid | hrstype | sum(hrs) |
+-------+---------+----------+
|     1 | REG     |   104.00 |
+-------+---------+----------+
1 row in set (0.00 sec)


It is better , not to assume , the behavior of the underlying DBMS.