This content originally appeared on DEV Community and was authored by Vahid Yousefzadeh
Oracle Database 23ai introduces several enhancements to the GROUP BY clause that simplify query syntax and improve readability, particularly when grouping by multiple columns.
Starting with Oracle Database 23ai (Release 23.3), the GROUP BY clause supports the following enhancements:
Column Alias in GROUP BY Clause: You can reference column aliases defined in the SELECT list directly in the GROUP BY clause.
Column Position in GROUP BY Clause: You can refer to columns in the GROUP BY clause using their position in the SELECT list.
These enhancements reduce redundancy and make SQL statements more concise and easier to maintain.
In Release Update 23.9, Oracle introduces an additional enhancement: the GROUP BY ALL clause.
The GROUP BY ALL clause eliminates the need to repeat all non-aggregate columns from the SELECT list in the GROUP BY clause. In previous releases, the GROUP BY clause required all non-aggregate columns to be listed explicitly, as shown below:
SELECT OWNER,
DATA_TYPE,
NULLABLE,
COUNT(*)
FROM DBA_TAB_COLUMNS
where owner='AUDSYS'
GROUP BY OWNER, DATA_TYPE, NULLABLE;
OWNER DATA_TYPE N COUNT(*)
-------------------- ------------------------- - ----------
AUDSYS VARCHAR2 Y 325
AUDSYS NUMBER Y 77
AUDSYS CLOB Y 14
AUDSYS RAW Y 8
AUDSYS TIMESTAMP(6) Y 8
AUDSYS NUMBER N 4
AUDSYS TIMESTAMP(6) N 1
7 rows selected.
With the new GROUP BY ALL syntax, the same query can be rewritten more concisely:
SQL> SELECT OWNER,
DATA_TYPE,
NULLABLE,
COUNT(*)
FROM DBA_TAB_COLUMNS
where owner='AUDSYS'
GROUP BY ALL;
OWNER DATA_TYPE N COUNT(*)
-------------------- ------------------------- - ----------
AUDSYS VARCHAR2 Y 325
AUDSYS NUMBER Y 77
AUDSYS CLOB Y 14
AUDSYS RAW Y 8
AUDSYS TIMESTAMP(6) Y 8
AUDSYS NUMBER N 4
AUDSYS TIMESTAMP(6) N 1
7 rows selected.
This produces the same result set while improving query clarity and reducing repetition.
This content originally appeared on DEV Community and was authored by Vahid Yousefzadeh