8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

GROUP BY and HAVING Clauses Using Column Aliases in Oracle Database 23c

From Oracle 23c onward we can use the column alias in GROUP BY and HAVING clauses, or the column position in the GROUP BY clause.

Setup
The Problem
The Solution : Column Alias
The Solution : Column Position
Query Transformation

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 23c 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 23c 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...

Back to the Top.