Oracle 26ai introduces GROUP BY ALL — a shorthand that tells Oracle to automatically include every non-aggregate column from your SELECT list in the grouping, so you never have to enumerate them by hand.

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 wider one it becomes repetitive — and fragile:

-- 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;         

Add a column to SELECT, forget to add it to GROUP BY, and Oracle throws ORA-00979: not a GROUP BY expression. It’s a class of error that shouldn’t exist.

The SQL execution order

GROUP BY ALL occupies the same position in the execution order as a conventional GROUP BY — it changes how grouping columns are derived, not when grouping happens:

FROM → WHERE → GROUP BY → HAVING → WINDOW → QUALIFY → DISTINCT → ORDER BY → FETCH FIRST

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;

When to stick with explicit GROUP BY

GROUP BY ALL is a readability and convenience feature — the query plan is unchanged. For very long queries, generated SQL, or codebases where clarity for the next reader matters more than brevity, the explicit form remains perfectly valid. Use whichever makes the intent clearer.