8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
GROUP BY and HAVING Clauses Using Column Aliases in Oracle Database 23ai
From Oracle 23ai onward we can use the column alias in GROUP BY
and HAVING
clauses, or the column position in the GROUP BY
clause.
Related articles.
Setup
The examples below use the following tables.
drop table if exists emp purge; drop table if exists dept purge; create table dept ( deptno number(2) constraint pk_dept primary key, dname varchar2(14), loc varchar2(13) ) ; create table emp ( empno number(4) constraint pk_emp primary key, ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2) constraint fk_deptno references dept ); insert into dept values (10,'ACCOUNTING','NEW YORK'); insert into dept values (20,'RESEARCH','DALLAS'); insert into dept values (30,'SALES','CHICAGO'); insert into dept values (40,'OPERATIONS','BOSTON'); insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20); insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20); insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30); insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10); insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20); insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10); insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20); insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30); insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20); insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10); commit;
The Problem
In previous releases of the database we were forced to repeat full references to the column definitions in the GROUP BY
and HAVING
clauses.
In the following example we want to present a formatted version of the department name, and get a count of the employees for those departments with more than 3 employees. Notice we need to repeat the "initcap(d.dname)" reference in the GROUP BY
clause and the "count(*)" reference in the HAVING
clause.
select initcap(d.dname) as department, count(*) as amount from dept d join emp e on d.deptno = e.deptno group by initcap(d.dname) having count(*) > 3; DEPARTMENT AMOUNT -------------- ---------- Research 5 Sales 6 SQL>
In this case both column references are quite small, but in some cases like analytic functions and case expressions they could be very large, making this repetition quite unwieldy.
The Solution : Column Alias
From Oracle 23ai onward we can make use of the column aliases in the GROUP BY
and HAVING
clauses. In this example, rather than repeating the "initcap(d.dname)" reference in the GROUP BY
clause, we use the column alias of "department". Likewise, instead of repeating the "count(*)" reference in the HAVING
clause, we use the column alias of "amount".
select initcap(d.dname) as department, count(*) as amount from dept d join emp e on d.deptno = e.deptno group by department having amount > 3; DEPARTMENT AMOUNT -------------- ---------- Research 5 Sales 6 SQL>
We no longer need to repeat complex column definitions in our GROUP BY
and HAVING
clauses.
The Solution : Column Position
From Oracle 23ai onward we can make use of the column position in the GROUP BY
clause. This functionality is not enabled by default, so it must be enabled at the session or system level. Here we enable it for our session.
alter session set group_by_position_enabled=true;
In this example, rather than repeating the "initcap(d.dname)" reference in the GROUP BY
clause, we use the column position of "1". We are not able to use the column position in the HAVING
clause for obvious reasons, as the database wouldn't know what was a column position and what was a number literal.
select initcap(d.dname) as department, count(*) as amount from dept d join emp e on d.deptno = e.deptno group by 1 having amount > 3; DEPARTMENT AMOUNT -------------- ---------- Research 5 Sales 6 SQL>
Personally I would avoid this and use the column alias in preference.
Query Transformation
Let's see what happens behind the scenes when we use this new syntax.
First we flush the shared pool and identify the trace file that will be created for our new session.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba alter system flush shared_pool; conn testuser1/testuser1@//localhost:1521/freepdb1 set linesize 100 column value format a65 select value from v$diag_info where name = 'Default Trace File'; VALUE ----------------------------------------------------------------- /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_23256.trc SQL>
Now we do a 10053 trace of the statement.
alter session set events '10053 trace name context forever'; select initcap(d.dname) as department, count(*) as amount from dept d join emp e on d.deptno = e.deptno group by department having amount > 3; alter session set events '10053 trace name context off';
We check the resulting trace file, searching for the section beginning with "Final query after transformations", and we see the following statement. I've reformatted it a little for readability.
Final query after transformations:******* UNPARSED QUERY IS ******* SELECT INITCAP("D"."DNAME") "DEPARTMENT", COUNT(*) "AMOUNT" FROM "TESTUSER1"."DEPT" "D", "TESTUSER1"."EMP" "E" WHERE "D"."DEPTNO"="E"."DEPTNO" GROUP BY INITCAP("D"."DNAME") HAVING COUNT(*)>3
The statement has been transformed in the following ways.
- The alias in the
GROUP BY
clause has been replaced by the full reference to "initicap(dname)". - The alias in the
HAVING
clause has been replaced by the full reference to "count(*)". - The ANSI join syntax has been replaced by the old-style Oracle join syntax. This is has always been the case for ANSI joins.
So allowing us to reference columns using aliases in the GROUP BY
and HAVING
clauses is syntax candy. We are able to write shorter and clearer SQL, and the optimizer converts it into the longer original syntax for us.
For more information see:
Hope this helps. Regards Tim...