BACKROUND
Short story
A few days ago I attended a presentation about a new oracle database feature . Along the lines the presenter mentioned the group by Alias and the group by all feature, One of the features I had mentioned in an earlier post my favorite features of oracle database 26ai. I didn’t write a complete blog on it of it’s own because well I guess I figured it’s one of the very nifty and useful features and well just about everyone will know about it by now. Plus lots of blogs have mentioned it already and there’s videos on youtube talking about this feature as well. Despite all that I was surprised when questions came in asking him to explain the group by all feature he just mentioned? Hmmmm! Anyways I guess there’s never too much info out there and you can always share something even if its been shared before. It has not been shared the way you may share yourself. So here goes this post, where I will be explaining the group by Alias and group by all features in Oracle database 26ai in my own words.
INTRODUCTION
The below is an extract from the documentation about this feature.
In SQL queries with complex SELECT lists that contain aggregation functions, the new GROUP BY ALL clause eliminates the need to put all non-aggregated columns into the GROUP BY clause. Instead, the new ALL keyword indicates that the results should be automatically grouped by all non-aggregated columns. You can now use column alias or SELECT item position in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses. Additionally the HAVING clause supports column aliases.
Business Benefit
Not having to repeat the non-aggregated columns in the GROUP BY clause, makes writing SQL queries quicker and less error prone. Users can use the GROUP BY ALL functionality to either quickly prototype their SQL query or for quick ad-hoc queries. These enhancements make it easier to write GROUP BY and HAVING clauses. It can make SQL queries much more readable and maintainable while providing better SQL code portability.
source : Oracle feature deails
USAGE
In order to demonstrate various examples of using this new clause let us create a sample table user_steps and insert some data.
SQL> CREATE TABLE IF NOT EXISTS user_steps (
user_id NUMBER
,date_recorded DATE
,username VARCHAR2(32 CHAR)
,district CHAR(1)
,steps NUMBER
,CONSTRAINT user_steps_pk PRIMARY KEY (user_id, date_recorded)
,CONSTRAINT user_steps_district_ck1 CHECK (district in ('E','W','N','S'))
) ;
Table USER_STEPS created.
SQL>
SQL> INSERT INTO user_steps (user_id, username,date_recorded,district,steps)
VALUES
(1,'hfungwi',DATE'2026-04-22','W',8763),
(1,'hfungwi',DATE'2026-04-23','W',10234),
(1,'hfungwi',DATE'2026-04-24','W',4021),
(1,'hfungwi',DATE'2026-04-25','W',6587),
(2,'kaeya',DATE'2026-04-22','N',20654),
(2,'kaeya',DATE'2026-04-23','N',13789),
(2,'kaeya',DATE'2026-04-24','N',9432),
(3,'Ebaron',DATE'2026-04-22','E',28623),
(3,'Ebaron',DATE'2026-04-23','E',21064),
(3,'Ebaron',DATE'2026-04-24','E',17437),
(3,'Ebaron',DATE'2026-04-25','E',8287),
(4,'AlexaN',DATE'2026-04-22','W',7230),
(4,'AlexaN',DATE'2026-04-23','W',9462),
(4,'AlexaN',DATE'2026-04-24','W',12236),
(5,'Nsharlyn',DATE'2026-04-22','W',23654) ;
15 rows inserted.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM user_steps ;
USER_ID DATE_RECORDED USERNAME DISTRICT STEPS
__________ ________________ ___________ ___________ ______________
1 22-APR-26 hfungwi W 8763
1 23-APR-26 hfungwi W 10234
1 24-APR-26 hfungwi W 4021
1 25-APR-26 hfungwi W 6587
2 22-APR-26 kaeya N 20654
2 23-APR-26 kaeya N 13789
2 24-APR-26 kaeya N 9432
3 22-APR-26 Ebaron E 28623
3 23-APR-26 Ebaron E 21064
3 24-APR-26 Ebaron E 17437
3 25-APR-26 Ebaron E 8287
4 22-APR-26 AlexaN W 7230
4 23-APR-26 AlexaN W 9462
4 24-APR-26 AlexaN W 12236
5 22-APR-26 Nsharlyn W 23654
15 rows selected.
SQL> Notice the use of the extended values constructor clause in the insert statement above.
Pre 26ai Group by
Given our fictional table above, consider a request to see the total number of steps for each user. Prior to oracle database 26ai , we can write the query as
SQL> SELECT username, sum(steps) total_steps
FROM USER_STEPS
GROUP BY username
ORDER BY 2 DESC;
USERNAME TOTAL_STEPS
___________ ______________
Ebaron 75411
kaeya 43875
hfungwi 29605
AlexaN 28928
Nsharlyn 23654
26ai Group by all
Now with oracle database 26ai, the following is valid syntax
SQL> SELECT username, sum(steps) total_steps
FROM USER_STEPS
GROUP BY ALL
ORDER BY 2 DESC;
USERNAME TOTAL_STEPS
___________ ______________
Ebaron 75411
kaeya 43875
hfungwi 29605
AlexaN 28928
Nsharlyn 23654 As shown above, the same results are returned without listing the non-aggregated column.
Grouping by aliases
If we wanted to express a username as a combination of that user’s user_id, username and district, we can do that easily and alias it as username_e for username extended. However in the group by, you need to to type out the full expression well at least that was the case in previous oracle versions. in oracle 26ai we can use aliases in the group by clause
Pre 26ai
Here’s a pre 26ai example:
SQL> SELECT user_id ||'-'|| username ||'-'|| district as username_e, sum(steps) total_steps
FROM USER_STEPS
GROUP BY user_id ||'-'|| username ||'-'|| district
ORDER BY 2 DESC;
USERNAME_E TOTAL_STEPS
_______________ ______________
3-Ebaron-E 75411
2-kaeya-N 43875
1-hfungwi-W 29605
4-AlexaN-W 28928
5-Nsharlyn-W 23654
oracle 26ai Example
Now the alias can be used in the group by clause as below
SQL> SELECT user_id ||'-'|| username ||'-'|| district as username_e, sum(steps) total_steps
FROM USER_STEPS
GROUP BY username_e
ORDER BY 2 DESC;
USERNAME_E TOTAL_STEPS
_______________ ______________
3-Ebaron-E 75411
2-kaeya-N 43875
1-hfungwi-W 29605
4-AlexaN-W 28928
5-Nsharlyn-W 23654
Having clause enhancements
The aliases can also be used in the having clause. While the aggregation used in this example is simple for clarity, scenarios exists where a complex expression is aggregated and using an alias greatly simplifies things. For example, say we want to return only users who have had more than 30,000 steps
pre 26ai example
Prior to 26ai we can resolve this using the having clause as shown below
SQL> SELECT user_id ||'-'|| username ||'-'|| district as username_e, sum(steps) total_steps
FROM USER_STEPS
GROUP BY username_e
HAVING sum(steps) >= 30000
ORDER BY 2 DESC;
USERNAME_E TOTAL_STEPS
_____________ ______________
3-Ebaron-E 75411
2-kaeya-N 43875 The above code requires the complete expression in the having clause. In 26ai things get a lot simpler
26ai example
the alias total_steps can be used in the having clause as shown below:
SQL> SELECT user_id ||'-'|| username ||'-'|| district as username_e, sum(steps) total_steps
FROM USER_STEPS
GROUP BY username_e
HAVING total_steps >= 30000
ORDER BY 2 DESC;
USERNAME_E TOTAL_STEPS
_____________ ______________
3-Ebaron-E 75411
2-kaeya-N 43875
SQL>
Group by column positions
notice in the examples up to this point, I have used the order by clause , specifically
order by 2Now, with oracle database 26ai, I can use a similar syntax for group by as well.
Imagine we want to figure out for each day, and each district the total number of steps taken. we can use the below code prior to 26ai to figure this out
QL> SELECT date_recorded, district, sum(steps) total_steps
FROM user_steps
GROUP by date_recorded, district
ORDER BY 3 DESC ;
DATE_RECORDED DISTRICT TOTAL_STEPS
________________ ___________ ______________
22-APR-26 W 39647
22-APR-26 E 28623
23-APR-26 E 21064
22-APR-26 N 20654
23-APR-26 W 19696
24-APR-26 E 17437
24-APR-26 W 16257
23-APR-26 N 13789
24-APR-26 N 9432
25-APR-26 E 8287
25-APR-26 W 6587
26ai solution
Now with oracle database 26ai, this can be re-written using the column position instead of the column names. In order to do this however, you have to set the group_by_position_enabled parameter to true at the session level.
SQL> ALTER SESSION SET group_by_position_enabled=true;
Session altered.
SQL>
SQL>
SQL>
SQL> SELECT date_recorded, district, sum(steps) total_steps
FROM user_steps
GROUP by 1 , 2
ORDER BY 3 DESC ;
DATE_RECORDED DISTRICT TOTAL_STEPS
________________ ___________ ______________
22-APR-26 W 39647
22-APR-26 E 28623
23-APR-26 E 21064
22-APR-26 N 20654
23-APR-26 W 19696
24-APR-26 E 17437
24-APR-26 W 16257
23-APR-26 N 13789
24-APR-26 N 9432
25-APR-26 E 8287
25-APR-26 W 6587
11 rows selected. For clarity and future maintenance of the code, using the column name or alias is probably preferable than using the position. Consider that when writing production code! However when doing ad hoc queries to find info quickly… position numbers all the way!
Having by position ?
Imagine we want to only see the date and district combinations where the total steps is atleast 20,000.
we can write a query like
SQL> SELECT date_recorded, district, sum(steps) total_steps
FROM user_steps
GROUP by 1 , 2
HAVING total_steps >= 20000
ORDER BY 3 DESC ;
DATE_RECORDED DISTRICT TOTAL_STEPS
________________ ___________ ______________
22-APR-26 W 39647
22-APR-26 E 28623
23-APR-26 E 21064
22-APR-26 N 20654 or prior to 26ai like
SQL> SELECT date_recorded, district, sum(steps) total_steps
FROM user_steps
GROUP by 1 , 2
HAVING sum(steps) >= 20000
ORDER BY 3 DESC ;
DATE_RECORDED DISTRICT TOTAL_STEPS
________________ ___________ ______________
22-APR-26 W 39647
22-APR-26 E 28623
23-APR-26 E 21064
22-APR-26 N 20654
however we cannot do a having by position like
SQL> SELECT date_recorded, district, sum(steps) total_steps
FROM user_steps
GROUP by 1 , 2
HAVING 3 >= 20000
ORDER BY 3 DESC ;
no rows selected
Our query returns no rows and when I look at it, the expression 3>=20000 cannot equate to true
Use in Summary Aggregations
Consider the initial request in this post, computing the total steps taken by every user. suppose we want to show each user, their steps and the total for all users. That can be done using the ROLLUP clause and in 26ai we can use column positions in rollup expressions
pre 26ai example
The below is how this can be done prior to 26ai
SQL> SELECT nvl(username,'TOTAL:') username , sum(steps) total_steps
FROM user_steps
GROUP BY ROLLUP (username)
ORDER BY case when nvl(username,'TOTAL:')= 'TOTAL:' then 2 else 1 end ASC, 2 DESC ;
USERNAME TOTAL_STEPS
___________ ______________
Ebaron 75411
kaeya 43875
hfungwi 29605
AlexaN 28928
Nsharlyn 23654
TOTAL: 201473
6 rows selected.
I can use the column position instead of the column name for the rollup such as:
SQL> SELECT nvl(username,'TOTAL:') username , sum(steps) total_steps
FROM user_steps
GROUP BY ROLLUP (1)
ORDER BY case when nvl(username,'TOTAL:')= 'TOTAL:' then 2 else 1 end ASC, 2 DESC ;
USERNAME TOTAL_STEPS
___________ ______________
Ebaron 75411
kaeya 43875
hfungwi 29605
AlexaN 28928
Nsharlyn 23654
201473
Apply Caution and Test Carefully
There are certain scenarios where group by ALL does not work as expected or fails entirely. Before changing all old code to use this new syntax, please test to ensure that it works. There is a blog showing an example of this here, as well as this nice youtube short on the feature.
CONCLUSION
So there you have it, my own telling of the GROUP BY ALIAS & GROUP BY ALL new feature in oracle database 26ai. I am quite happy about this feature as it is something I have wished to have in the database for a long time. Hopefully the examples above made it easy for you to understand this feature as well and kindly leave a comment if you feel so inclined.
Thanks for reading.
Harris.
References & Further Reading
Oracle base
Oracle features
oracle docs
connor mcdonald’s blog
sql features github