syntax and example of GROUP_ID function in oracle sql

The GROUP_ID function in Oracle SQL / PLSQL is used to assign a unique group id to each group returned by a SQL / PLSQL SELECT statement using GROUP BY Clause.

GROUP_ID is mainly used to identify duplicate groups in the query results, the GROUP_ID function in Oracle SQL / PLSQL will return 0 for each unique group and whenever a duplicate group is found the GROUP_ID function will return a value that is > 0.

Syntax for the GROUP_ID function in Oracle SQL / PLSQL is:
SELECT column(s)
FROM table_name
WHERE condition(s)
GROUP BY column(s);

Example:
Using GROUP_ID in Oracle SQL / PLSQL Select Statement.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

If we write our query as:

 SELECT employee_name
,commission
,salary
,GROUP_ID()
FROM employee
GROUP BY commission
         ,salary
         ,employee_name
         ,ROLLUP(commission,salary);

We will get the following output:

Employee_ID	Commission	Salary	GROUP_ID
Emp H		12000	0
Emp B	20	20000	0
Emp G		12000	0
Emp A		10000	0
Emp C	20	28000	0
Emp D	5	30000	0
Emp E	10	32000	0
Emp F	5	20000	0
Emp H		12000	2
Emp B	20	20000	2
Emp G		12000	2
Emp A		10000	2
Emp C	20	28000	2
Emp D	5	30000	2
Emp E	10	32000	2
Emp F	5	20000	2
Emp H		12000	1
Emp B	20	20000	1
Emp G		12000	1
Emp A		10000	1
Emp C	20	28000	1
Emp D	5	30000	1
Emp E	10	32000	1
Emp F	5	20000	1

We can use the HAVING CLAUSE to remove the duplicate groups from the above fetched records as:

SELECT employee_name
,commission
,salary
,GROUP_ID()
FROM employee
GROUP BY commission
         ,salary
         ,employee_name
         ,ROLLUP(commission,salary)
HAVING GROUP_ID() &lt; 1;

We will get the following output:

Employee_ID	Commission	Salary	GROUP_ID
Emp H		12000	0
Emp B	20	20000	0
Emp G		12000	0
Emp A		10000	0
Emp C	20	28000	0
Emp D	5	30000	0
Emp E	10	32000	0
Emp F	5	20000	0

Here we can observe that we have fetched only unique group of records using HAVING in Oracle SQL / PLSQL SELECT statement.