Oracle 26ai introduces GROUP BY ALL which is syntactic sugar to tell Oracle to automatically include every non-aggregate column from your SELECT list in the grouping, so you don’t have add them again in the GROUP BY clause.

The problem

Standard SQL requires every non-aggregate column in the SELECT list to appear explicitly in the GROUP BY clause. For a short query that’s fine. For a longer one it becomes repetitive and it is easy to omit a column resulting in a ORA-00979: not a GROUP BY expression.

-- Headcount by department and job
-- Every non-aggregate column must be listed in GROUP BY
SELECT department_id,
       job_id,       
       COUNT(*) AS headcount       
FROM   employees
GROUP BY department_id, 
         job_id;         

The 26ai solution

GROUP BY ALL removes the repetition entirely. Oracle inspects the SELECT list, identifies every column that isn’t inside an aggregate function, and groups by all of them automatically:

-- GROUP BY ALL: Oracle derives the grouping columns 
-- from the SELECT list
SELECT department_id,
       job_id,       
       COUNT(*) AS headcount       
FROM   employees
GROUP BY ALL; 

The result set is identical. The query plan is identical. You’ve just stopped maintaining the same column list twice.

Works with expressions too

GROUP BY ALL groups by the underlying expression, not the alias. This means derived columns behave exactly as if you’d written them out in a conventional GROUP BY:

-- Annual hire trend: group by the year 
-- extracted from hire_date
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year,
       department_id,
       COUNT(*)                     AS new_hires
FROM   employees
GROUP BY ALL;

Equivalent to GROUP BY EXTRACT(YEAR FROM hire_date), department_id Oracle resolves it for you. Note: Oracle groups on the expression itself, not the alias. So hire_year is not what’s grouped on EXTRACT(YEAR FROM hire_date) is.

HAVING still works as normal

GROUP BY ALL only changes how grouping columns are derived. HAVING filtering on aggregate results is unaffected:

-- Departments where average salary exceeds 8000
SELECT department_id,
       ROUND(AVG(salary), 2) AS avg_salary,
       COUNT(*)              AS headcount
FROM   employees
GROUP BY ALL
HAVING AVG(salary) > 8000;