GROUP BY Column Alias- A new feature in Oracle 23ai

  

Starting from Oracle 23ai, you can specify column aliases or positions in GROUP BY clause. You can also specify aliases for aggregate functions as well in HAVING clause. You can also  use column alias in GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses.  Let us demontsrate the same with some examples.

Let's create a table in 19c database. SQL> select banner from v$version; BANNER ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production #Create a table and insert some values to perform aggregate functions. SQL> create table products(p_id number,p_name varchar2(20),p_cost number,p_date date); Table created. SQL> insert into products values(1,'P1',5000,'05-JAN-2024'); 1 row created. SQL> insert into products values(2,'P1',7000,'10-JAN-2024'); 1 row created. SQL> insert into products values(3,'P1',7000,'15-JAN-2024'); 1 row created. SQL> insert into products values(4,'P2',21000,'05-FEB-2024'); 1 row created. SQL> insert into products values(5,'P2',21000,'10-FEB-2024'); 1 row created. SQL> insert into products values(6,'P3',33000,'08-MAR-2024'); 1 row created. SQL> insert into products values(7,'P3',33000,'27-MAR-2024'); 1 row created. SQL> insert into products values(8,'P4',55000,'04-APR-2024'); 1 row created. SQL> insert into products values(9,'P5',60000,'31-MAY-2024'); 1 row created. SQL> insert into products values(10,'P6',75000,'01-JUN-2024'); 1 row created. SQL> commit; Commit complete. #Query the table to check the data. SQL> select * from products;       P_ID P_NAME                   P_COST P_DATE ---------- -------------------- ---------- ---------          1 P1                         5000 05-JAN-24          2 P1                         7000 10-JAN-24          3 P1                         7000 15-JAN-24          4 P2                        21000 05-FEB-24          5 P2                        21000 10-FEB-24          6 P3                        33000 08-MAR-24          7 P3                        33000 27-MAR-24          8 P4                        55000 04-APR-24          9 P5                        60000 31-MAY-24         10 P6                        75000 01-JUN-24 10 rows selected. #Now execute below query to list the number of products whose count is greater than 1. SQL> select p_name,count(*) from products group by p_name having count(*)>1; P_NAME                 COUNT(*) -------------------- ---------- P1                            3 P2                            2 P3                            2 #Now execute below query to count the product cost whose count is greater than 1. SQL> select p_cost,count(*) from products group by p_cost having count(*)>1;     P_COST   COUNT(*) ---------- ----------      21000          2       7000          2      33000          2   In above both the queries, we added filter condition on aggragate function in having clause to get the desired output. Now let's try to use aliases for the aggregate condition. SQL> select p_name,count(*) ct from products group by p_name having ct>1; select p_name,count(*) ct from products group by p_name having ct>1                                                                * ERROR at line 1: ORA-00904: "CT": invalid identifier SQL> select p_cost,count(*) ct from products group by p_cost having ct>1; select p_cost,count(*) ct from products group by p_cost having ct>1                                                                * ERROR at line 1: ORA-00904: "CT": invalid identifier In the above both queries, It does not allow to use aliases for aggregate conditions till 19c release. Oracle 23ai introduced new feature that allows you to add aliases in group by and having clauses. You will face "ORA-00904: invalid identifier" error message. Let's try similar example in Oracle 23ai release database. Let's create a table in Oracle 23ai database. SQL> select banner from v$version; BANNER ----------------------------------------------------------- Oracle Database 23ai Free Release 23.0.0.0.0 - Production #Create a table and insert some values to perform aggregate functions. SQL> create table products(p_id number,p_name varchar2(20),p_cost number,p_date date); Table created. SQL> insert into products values(1,'P1',5000,'05-JAN-2024'); 1 row created. SQL> insert into products values(2,'P1',7000,'10-JAN-2024'); 1 row created. SQL> insert into products values(3,'P1',7000,'15-JAN-2024'); 1 row created. SQL> insert into products values(4,'P2',21000,'05-FEB-2024'); 1 row created. SQL> insert into products values(5,'P2',21000,'10-FEB-2024'); 1 row created. SQL> insert into products values(6,'P3',33000,'08-MAR-2024'); 1 row created. SQL> insert into products values(7,'P3',33000,'27-MAR-2024'); 1 row created. SQL> insert into products values(8,'P4',55000,'04-APR-2024'); 1 row created. SQL> insert into products values(9,'P5',60000,'31-MAY-2024'); 1 row created. SQL> insert into products values(10,'P6',75000,'01-JUN-2024'); 1 row created. SQL> commit; Commit complete. #Query the table to check the data. SQL> select * from products;       P_ID P_NAME                   P_COST P_DATE ---------- -------------------- ---------- ---------          1 P1                         5000 05-JAN-24          2 P1                         7000 10-JAN-24          3 P1                         7000 15-JAN-24          4 P2                        21000 05-FEB-24          5 P2                        21000 10-FEB-24          6 P3                        33000 08-MAR-24          7 P3                        33000 27-MAR-24          8 P4                        55000 04-APR-24          9 P5                        60000 31-MAY-24         10 P6                        75000 01-JUN-24 10 rows selected. #Now execute below query to list the number of products whose count is greater than 1. SQL> select p_name,count(*) from products group by p_name having count(*)>1; P_NAME                 COUNT(*) -------------------- ---------- P1                            3 P2                            2 P3                            2 #Now execute below query to count the product cost whose count is greater than 1. SQL> select p_cost,count(*) from products group by p_cost having count(*)>1;     P_COST   COUNT(*) ---------- ----------       7000          2      21000          2      33000          2 In above both the queries, we added filter condition on aggragate function in having clause to get the desired output similar to 19c. Now let's try to use aliases for the aggregate condition. SQL> select p_name,count(*) ct from products group by p_name having ct>1; P_NAME                       CT -------------------- ---------- P1                            3 P2                            2 P3                            2 SQL> select p_cost,count(*) ct from products group by p_cost having ct>1;     P_COST         CT ---------- ----------       7000          2      21000          2      33000          2 Here, you can see in the above examples, Oracle 23ai allowed you to add column aliases in GROUP BY cluase.

Let's try more examples of GROUP BY clause in Oracle 23ai database. In 19c DB Release: SQL> select p_name p,max(p_cost) from products group by p; select p_name p,max(p_cost) from products group by p                                                    * ERROR at line 1: ORA-00904: "P": invalid identifier In Oracle 23ai: SQL> select p_name p,max(p_cost) from products group by p; P                    MAX(P_COST) -------------------- ----------- P1                          7000 P2                         21000 P3                         33000 P4                         55000 P5                         60000 P6                         75000 6 rows selected. Let's try more examples with multiple column aliases in GROUP BY clause. In 19c DB Release: SQL> select p_id a,p_name b,max(p_cost) from products group by a,b; select p_id a,p_name b,max(p_cost) from products group by a,b                                                             * ERROR at line 1: ORA-00904: "B": invalid identifier In Oracle 23ai: SQL> select p_id a,p_name b,max(p_cost) from products group by a,b;          A B                    MAX(P_COST) ---------- -------------------- -----------          1 P1                          5000          2 P1                          7000          3 P1                          7000          4 P2                         21000          5 P2                         21000          6 P3                         33000          7 P3                         33000          8 P4                         55000          9 P5                         60000         10 P6                         75000 10 rows selected.

Let's try more examples of GROUP BY CUBE clause in Oracle 23ai database. In 19c DB Release: SQL> select p_name n,p_date d,max(p_cost) from products group by cube(n,d); select p_name n,p_date d,max(p_cost) from products group by cube(n,d)                                                                    * ERROR at line 1: ORA-00904: "D": invalid identifier In Oracle 23ai: SQL> select p_name n,p_date d,max(p_cost) from products group by cube(n,d); N                    D         MAX(P_COST) -------------------- --------- -----------                                      75000                      05-JAN-24        5000                      10-JAN-24        7000                      15-JAN-24        7000                      05-FEB-24       21000                      10-FEB-24       21000                      08-MAR-24       33000                      27-MAR-24       33000                      04-APR-24       55000                      31-MAY-24       60000                      01-JUN-24       75000 P1                                    7000 P1                   05-JAN-24        5000 P1                   10-JAN-24        7000 P1                   15-JAN-24        7000 P2                                   21000 P2                   05-FEB-24       21000 P2                   10-FEB-24       21000 P3                                   33000 P3                   08-MAR-24       33000 P3                   27-MAR-24       33000 P4                                   55000 P4                   04-APR-24       55000 P5                                   60000 P5                   31-MAY-24       60000 P6                                   75000 P6                   01-JUN-24       75000 27 rows selected. In the above examples, you can see that aliases are allowed in the GROUP BY CUBE cluase in Oracle 23ai, whereas older releases will result in an error message.

Let's try more examples of GROUP BY ROLLUP clause in Oracle 23ai database. In 19c DB Release: SQL> select p_name n,p_date d,max(p_cost) from products group by rollup(n,d); select p_name n,p_date d,max(p_cost) from products group by rollup(n,d)                                                                      * ERROR at line 1: ORA-00904: "D": invalid identifier In Oracle 23ai:  SQL> select p_name n,p_date d,max(p_cost) from products group by rollup(n,d); N                    D         MAX(P_COST) -------------------- --------- ----------- P1                   05-JAN-24        5000 P1                   10-JAN-24        7000 P1                   15-JAN-24        7000 P2                   05-FEB-24       21000 P2                   10-FEB-24       21000 P3                   08-MAR-24       33000 P3                   27-MAR-24       33000 P4                   04-APR-24       55000 P5                   31-MAY-24       60000 P6                   01-JUN-24       75000 P1                                    7000 P2                                   21000 P3                                   33000 P4                                   55000 P5                                   60000 P6                                   75000                                      75000 17 rows selected. In the above examples, you can see that aliases are allowed in the GROUP BY ROLLUP cluase in Oracle 23ai, whereas older releases will result in an error message.

Let's try more examples of GROUP BY GROUPING SETS clause in Oracle 23ai database. In 19c DB Release: SQL> select p_name n,p_date d,count(*) from products group by grouping sets(n,d); select p_name n,p_date d,count(*) from products group by grouping sets(n,d)                                                                          * ERROR at line 1: ORA-00904: "D": invalid identifier In Oracle 23ai:  SQL> select p_name n,p_date d,count(*) from products group by grouping sets(n,d); N                    D           COUNT(*) -------------------- --------- ----------                      05-JAN-24          1                      10-JAN-24          1                      15-JAN-24          1                      05-FEB-24          1                      10-FEB-24          1                      08-MAR-24          1                      27-MAR-24          1                      04-APR-24          1                      31-MAY-24          1                      01-JUN-24          1 P2                                      2 P3                                      2 P4                                      1 P5                                      1 P6                                      1 P1                                      3 16 rows selected. In the above examples, you can see that aliases are allowed in the GROUP BY GROUPING SETS cluase in Oracle 23ai, whereas older releases will result in an error message.

Visit "Oracle Database 23ai Free - All in one page" to access all blog posts on Oracle 23ai in one page.

Thanks for reading this post ! Please comment if you like this post ! Click FOLLOW to get future blog updates !