Oracle/PLSQL: Group_ID Function

In Oracle/PLSQL, the group_id function assigns a number to each group resulting from a GROUP BY clause. The group_id function is most commonly used to identify duplicated groups in your query results.

For each unique group, the group_id function will return 0. When a duplicated group is found, the group_id function will return a value >0.

The syntax for the group_id function is:

select column1, column2, ... column_n, GROUP_ID()
from tables
where predicates
GROUP BY column1, column2, ... column_n;

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

For example:

select sum(salary), department, bonus, GROUP_ID()
from employees
where bonus > 100
GROUP BY department,
ROLLUP (department, bonus);

You could use the HAVING clause to eliminated duplicated groups as follows:

select sum(salary), department, bonus, GROUP_ID()
from employees
where bonus > 100
GROUP BY department,
ROLLUP (department, bonus)
HAVING GROUP_ID() < 1;