Oracle GROUP_ID function

Preamble

Oracle/PLSQL GROUP_ID function assigns a number to each group as a result of GROUP BY operator. GROUP_ID function is most often used to identify duplicate groups in query results.

Syntax of Oracle/PLSQL function GROUP_ID

SELECT column1_id, column2_id, ... column_n_id, GROUP_ID()
FROM tabs
WHERE conds
GROUP BY column1_id, column2_id, ... column_n_id;

No parameters or arguments for GROUP_ID function.

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

Let’s consider some examples of Oracle GROUP_ID function and learn how to use GROUP_ID function in Oracle PLSQL.

SELECT SUM(salary_id),
Depart,
bonus,
GROUP_ID()
FROM empls
WHERE bonus > 100
GROUP BY depart,
ROLLUP (depart, bonus);

You can use the HAVING operator to eliminate duplicate groups as follows:

SELECT SUM(salary),
Depart,
bonus,
GROUP_ID()
FROM empls
WHERE bonus > 100
GROUP BY depart,
ROLLUP (depart, bonus)
HAVING GROUP_ID() < 1;

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​