Oracle / PLSQL: GROUP_ID Function

This Oracle tutorial explains how to use the Oracle/PLSQL GROUP_ID function with syntax and examples.

Description

The Oracle/PLSQL 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.

Syntax

The syntax for the GROUP_ID function in Oracle/PLSQL is:

SELECT column1, column2, ... column_n, GROUP_ID()
FROM tables
WHERE conditions
GROUP BY column1, column2, ... column_n;

Parameters or Arguments

There are no parameters or arguments for the GROUP_ID function.

Returns

The GROUP_ID function returns a numeric value.

Applies To

The GROUP_ID function can be used in the following versions of Oracle/PLSQL:

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

Example

Let's look at some Oracle GROUP_ID function examples and explore how to use the GROUP_ID function in Oracle/PLSQL.

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;