You have probably seen a couple of cool GROUP BY features that came with Oracle Database 26ai.
You can check out the short video below
but since you’re on a blog, here’s the TL;DR – You can use the column alias in your GROUP BY, to avoid the annoying duplication of code
Old-school way
SQL> select
2 case
3 when job = 'SALES' then trunc(sal/100)
4 when hiredate > date '1981-02-01' then trunc(nvl(comm,300)/100)
5 else ceil(sal/80)
6 end bonus,
7 count(*)
8 from emp
9 group by
10 case
11 when job = 'SALES' then trunc(sal/100)
12 when hiredate > date '1981-02-01' then trunc(nvl(comm,300)/100)
13 else ceil(sal/80)
14 end;
BONUS COUNT(*)
---------- ----------
10 1
3 11
5 1
14 1
15 1
38 1
40 1
Cool New Way
SQL> select
2 case
3 when job = 'SALES' then trunc(sal/100)
4 when hiredate > date '1981-02-01' then trunc(nvl(comm,300)/100)
5 else ceil(sal/80)
6 end bonus,
7 count(*)
8 from emp
9 group by bonus;
BONUS COUNT(*)
---------- ----------
10 1
3 11
5 1
14 1
15 1
38 1
40 1
Grouping by an alias is cool but that doesn’t really help in the cases where you are grouping by a large number of columns. In the example below, I still have to copy-paste the entire column list into my GROUP BY clause.
SQL> select
2 owner
3 ,object_type
4 ,created
5 ,last_ddl_time
6 ,timestamp
7 ,status
8 ,temporary
9 ,generated
10 ,secondary
11 ,namespace
12 ,edition_name
13 ,sharing
14 ,editionable
15 ,oracle_maintained
16 ,application
17 ,default_collation
18 ,duplicated
19 ,sharded
20 ,imported_object
21 ,synchronous_duplicated
22 ,created_appid
23 ,created_vsnid
24 ,COUNT(*)
25 from t
26 group by
27 owner
28 ,subobject_name
29 ,object_type
30 ,created
31 ,last_ddl_time
32 ,timestamp
33 ,status
34 ,temporary
35 ,generated
36 ,secondary
37 ,namespace
38 ,edition_name
39 ,sharing
40 ,editionable
41 ,oracle_maintained
42 ,application
43 ,default_collation
44 ,duplicated
45 ,sharded
46 ,imported_object
47 ,synchronous_duplicated
48 ,created_appid
49 ,created_vsnid
50 /
OWNER OBJEC CREATED LAST_DDL_ TIME STATU T G S NAMES EDITION_ S E O APP D D S IM S CREATED_APPID CREATED_VSNID COUNT(*)
---------- ----- --------- --------- ---- ----- - - - ----- -------- - - - --- - - - -- - ------------- ------------- ----------
SCOTT TABLE 01-JAN-24 01-JAN-24 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 79531
SCOTT TABLE 01-JAN-24 01-JAN-25 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 3069
SCOTT TABLE 01-JAN-24 01-JAN-26 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 1770
SCOTT TABLE 01-JAN-26 01-JAN-26 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 5452
SCOTT TABLE 01-JAN-25 01-JAN-25 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 4505
SCOTT TABLE 01-JAN-25 01-JAN-26 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 1449
6 rows selected.
Over the years, many developers have lamented both publicly and directly to me. “Surely the database can just work out what the appropriate GROUP BY should be“, and fortunately, we have extended GROUP BY further to make this a lot simpler. Just add a GROUP BY ALL and you’re done!
SQL> select
2 owner
3 ,object_type
4 ,created
5 ,last_ddl_time
6 ,timestamp
7 ,status
8 ,temporary
9 ,generated
10 ,secondary
11 ,namespace
12 ,edition_name
13 ,sharing
14 ,editionable
15 ,oracle_maintained
16 ,application
17 ,default_collation
18 ,duplicated
19 ,sharded
20 ,imported_object
21 ,synchronous_duplicated
22 ,created_appid
23 ,created_vsnid
24 ,count(*)
25 from t
26 GROUP BY ALL;
OWNER OBJEC CREATED LAST_DDL_ TIME STATU T G S NAMES EDITION_ S E O APP D D S IM S CREATED_APPID CREATED_VSNID COUNT(*)
---------- ----- --------- --------- ---- ----- - - - ----- -------- - - - --- - - - -- - ------------- ------------- ----------
SCOTT TABLE 01-JAN-24 01-JAN-24 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 79531
SCOTT TABLE 01-JAN-24 01-JAN-25 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 3069
SCOTT TABLE 01-JAN-24 01-JAN-26 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 1770
SCOTT TABLE 01-JAN-26 01-JAN-26 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 5452
SCOTT TABLE 01-JAN-25 01-JAN-25 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 4505
SCOTT TABLE 01-JAN-25 01-JAN-26 2025 VALID N N N TABLE N Y N YES N N NO N 123 456 1449
6 rows selected.
So that’s the end of the story right? Every single SQL that needs a grouping should just have GROUP BY ALL ?
Unfortunately not. Before you open up every SQL statement in your code repository and replace it with GROUP BY ALL, there are some nuances you need to be aware of.
Consider the following example. There are two non-aggregated columns in the SELECT list, but because one determines the other, we only need a single column in the GROUP BY clause.
SQL> select deptno, deptno+1, sum(sal)
2 from emp
3 group by deptno;
DEPTNO DEPTNO+1 SUM(SAL)
---------- ---------- ----------
20 21 10875
30 31 10600
10 11 8750
40 41 6200
This is not a show-stopper – the query will run fine even if we add in the redundant columns
SQL> select deptno, deptno+1, sum(sal)
2 from emp
3 group by deptno, deptno+1;
DEPTNO DEPTNO+1 SUM(SAL)
---------- ---------- ----------
20 21 10875
30 31 10600
10 11 8750
40 41 6200
which also means that it will run fine if we swap out the grouping columns with a GROUP BY ALL
SQL> select deptno, deptno+1, sum(sal)
2 from emp
3 group by all;
DEPTNO DEPTNO+1 SUM(SAL)
---------- ---------- ----------
20 21 10875
30 31 10600
10 11 8750
40 41 6200
But consider the following SQL to give a list of employees per department.
SQL> select deptno||':'||listagg(ename,',') within group (order by empno) emplist
2 from emp
3 group by deptno;
EMPLIST
----------------------------------------------------------------------------------------------------------------------------------
10:CLARK,KING,MILLER
20:SMITH,JONES,SCOTT,ADAMS,FORD
30:ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
This looks like a candidate to replace with GROUP BY ALL, but we’re in for a nasty surprise
SQL> select deptno||':'||listagg(ename,',') within group (order by empno) emplist
2 from emp
3 group by all;
select deptno||':'||listagg(ename,',') within group (order by empno) emplist
*
ERROR at line 1:
ORA-00937: not a single-group group function
To understand what is happening, we need to understand that GROUP BY ALL is still in draft mode in the SQL standard, but essentially the specification is along the lines of:
“GROUP BY ALL expands to elements of the select list that do not contain aggregate functions.”
I suspect the intent of the specification was to be the same intuitive way that we would interpret it, namely, GROUP BY ALL would just become the only clause you would ever need, but for that we probably needed more precision in the spec.
When we consider the phrase: “elements of the select list that do not contain aggregate functions” in conjunction with our failing SQL statement, there is some ambiguity:
- ENAME is definitely an element that contains an aggregation, ie, LISTAGG(ENAME)
- However, DEPTNO is also an element that “contains” an aggregation, because the ‘element’ is DEPTNO||LISTAGG(…) not just DEPTNO
Hence, both the select elements are not transposed down to the GROUP BY, and hence the GROUP BY ALL does not work in our example
We can see this by using DBMS_UTILITY to expand the SQL text of a simple GROUP BY ALL.
SQL> variable c clob
SQL> begin
2 dbms_utility.expand_sql_text(
3 'select deptno, job||sum(sal) from emp group by all',
4 :c);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> set long 1000
SQL> print c
C
--------------------------------------------------------------------------------
SELECT "A1"."DEPTNO" "DEPTNO","A1"."JOB"||SUM("A1"."SAL") "JOB||SUM(SAL)" FROM "
ADMIN"."EMP" "A1" GROUP BY "A1"."DEPTNO"
In this SQL, DEPTNO is not an aggregation element, but JOB is an aggregation element even though it in itself is not aggregated.
Note: If you’re thinking that you could go with a “hybrid” approach by adding JOB to the ALL clause, you are out of luck
SQL> select deptno, job||sum(sal)
2 from emp
3 group by all, job;
group by all, job
*
ERROR at line 3:
ORA-03048: SQL reserved word ',' is not syntactically valid following '...sum(sal)
from emp
group by all'
SQL> select deptno, job||sum(sal)
2 from emp
3 group by job,all;
group by job,all
*
ERROR at line 3:
ORA-00936: missing expression
So while GROUP BY ALL is a cool convenience for developers, be aware that it is not a 100% fit for all grouping queries.
More Reading
- https://peter.eisentraut.org/blog/2025/11/11/waiting-for-sql-202y-group-by-all
- https://modern-sql.com/caniuse/group-by-all
- https://community.sap.com/t5/technology-blog-posts-by-sap/why-the-sql-standard-does-not-need-another-way-to-do-group-by/ba-p/13204437
- https://www.javacodegeeks.com/2015/05/how-sql-group-by-should-have-been-designed-like-neo4js-implicit-group-by.html
Leave a Reply