Friday, January 11, 2013

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;