Group_ID Function

In Oracle/PLSQL, the group_id function assigns a number to each group resulting from an SQL 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.

Syntax

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 11g, Oracle 10g, Oracle 9i

For Example

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

You could use the SQL 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;