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;
Advertisements