GROUP BY Column Alias or Position in Oracle 23ai

In Oracle 23ai database you can now GROUP BY using an alias or column position. This means you no longer have to write aggregation groups containing certain expressions several times and it will just simplify your SQL query writing.

GROUP BY alias

SELECT manager_id as mgr, count(*)
FROM employees
GROUP BY mgr;

GROUP BY position

You do have to enable group by position through an alter session command first.

ALTER SESSION SET GROUP_BY_POSITION_ENABLED=TRUE;

SELECT extract(year from hire_date) as year_hired, count(*)
FROM employees
GROUP BY 1;

HAVING alias

SELECT extract(year from hire_date) as as h_date, count(*)
FROM employees
GROUP BY h_date
HAVING h_date > 1982;