In a new feature in Oracle Database 23ai, the GROUP BY clause now allows us to use column aliases as well as position values in the GROUP BY clause.
Pre Oracle Database 23ai
Column excluded from GROUP BY clause. Do we know which column is missing in the GROUP BY clause?
SQL> select owner,segment_name,segment_type,sum(bytes)/1048576 from dba_segments group by owner,segment_type;
select owner,segment_type,sum(bytes)/1048576 from dba_segments group by owner
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Using a column alias returns an error.
SQL> select owner,segment_type "Object Type",sum(bytes)/1048576 from dba_segments group by owner, "Object Type";
select owner,segment_type "Object Type",sum(bytes)/1048576 from dba_segments group by owner, "Object Type"
*
ERROR at line 1:
ORA-00904: "Object Type": invalid identifier
Using POSITION in GROUP BY clause also returns an error.
SQL> select owner,segment_type "Object Type",sum(bytes)/1048576 from dba_segments group by 1,2;
select owner,segment_type "Object Type",sum(bytes)/1048576 from dba_segments group by 1,2
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Oracle Database 23ai
Error message now provides information about the column which needs to be included in the GROUP BY clause.
SQL> select owner,segment_type,sum(bytes)/1048576 from dba_segments group by owner;
select owner,segment_type,sum(bytes)/1048576 from dba_segments group by owner
*
ERROR at line 1:
ORA-00979: "SEGMENT_TYPE": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/
Using Column alias – now supported in 23ai!
SQL> select owner,segment_type "Object Type",sum(bytes)/1048576 from dba_segments group by owner, "Object Type";
OWNER Object Type SUM(BYTES)/1048576
------------------------------ ------------------ ------------------
SYS CLUSTER 113.25
SYS INDEX 418.125
SYS TABLE 759.75
SYS LOBINDEX 21.1875
SYS LOBSEGMENT 210.5625
OUTLN TABLE .1875
OUTLN INDEX .25
OUTLN LOBINDEX .0625
OUTLN LOBSEGMENT .0625
SYSTEM TABLE 1.75
SYSTEM INDEX 1.5
Using Column position – now supported in 23ai! -note the parameter GROUP_BY_POSITION_ENABLED.
SQL> show parameter group_by_position_enabled
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
group_by_position_enabled boolean FALSE
SQL> ALTER SESSION SET group_by_position_enabled=TRUE;
Session altered.
SQL> select owner,segment_type,sum(bytes)/1048576 from dba_segments group by 1,2;
OWNER SEGMENT_TYPE SUM(BYTES)/1048576
------------------------------ ------------------ ------------------
SYS CLUSTER 113.25
SYS INDEX 418.125
SYS TABLE 759.75
SYS LOBINDEX 21.1875
SYS LOBSEGMENT 210.5625
OUTLN TABLE .1875
OUTLN INDEX .25
OUTLN LOBINDEX .0625
OUTLN LOBSEGMENT .0625
SYSTEM TABLE 1.75
SYSTEM INDEX 1.5
