GROUP BY ALL: The Shorthand That Changes Everything

Here’s one of my favorite SQL improvements in Oracle 23ai, and one with a personal history I’ll share more about in September: GROUP BY ALL.

Every developer who has written aggregate queries knows the pain. You write a SELECT with some aggregate functions (SUM, COUNT, AVG), and Oracle forces you to list every non-aggregated column in the GROUP BY clause. Add a column to the SELECT? Add it to GROUP BY too. Forget one? ORA-00979: not a GROUP BY expression.

GROUP BY ALL solves this automatically. It groups by every non-aggregate expression in the SELECT list.

Before (Oracle ≤ 19c / 21c):

SELECT department_id, department_name, job_id, location_id,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM   employees
GROUP BY department_id, department_name, job_id, location_id;

After (Oracle 23ai):

SELECT department_id, department_name, job_id, location_id,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM   employees
GROUP BY ALL;

Oracle automatically groups by department_id, department_name, job_id, and location_id — every column that isn’t inside an aggregate function.

Adding a new column? No GROUP BY change needed:

SELECT department_id, department_name, job_id, location_id, country_id,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM   employees
GROUP BY ALL;  -- country_id is automatically included

Works with ROLLUP, CUBE, and GROUPING SETS too:

SELECT department_id, job_id,
       SUM(salary)
FROM   employees
GROUP BY ROLLUP ALL;

This feature is one of those “why didn’t this always exist?” improvements. Less boilerplate, fewer bugs from missing a column in GROUP BY, and queries that are easier to extend. I’ll have more to say about the origins of this idea in September’s special series.

Author: matheusdba