• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / GROUP_ID Function in Oracle SQL – PLSQL

GROUP_ID Function in Oracle SQL – PLSQL

November 27, 2012 by techhoneyadmin

The GROUP_ID function in Oracle SQL / PLSQL is used to assign a unique group id to each group returned by a SQL / PLSQL SELECT statement using GROUP BY Clause.

GROUP_ID is mainly used to identify duplicate groups in the query results, the GROUP_ID function in Oracle SQL / PLSQL will return 0 for each unique group and whenever a duplicate group is found the GROUP_ID function will return a value that is > 0.

Syntax for the GROUP_ID function in Oracle SQL / PLSQL is:
SELECT column(s)
FROM table_name
WHERE condition(s)
GROUP BY column(s);

Example:
Using GROUP_ID in Oracle SQL / PLSQL Select Statement.

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

If we write our query as:

 SELECT employee_name
,commission
,salary
,GROUP_ID()
FROM employee
GROUP BY commission
         ,salary
         ,employee_name
         ,ROLLUP(commission,salary);

We will get the following output:

Employee_ID Commission Salary GROUP_ID
Emp H 12000 0
Emp B 20 20000 0
Emp G 12000 0
Emp A 10000 0
Emp C 20 28000 0
Emp D 5 30000 0
Emp E 10 32000 0
Emp F 5 20000 0
Emp H 12000 2
Emp B 20 20000 2
Emp G 12000 2
Emp A 10000 2
Emp C 20 28000 2
Emp D 5 30000 2
Emp E 10 32000 2
Emp F 5 20000 2
Emp H 12000 1
Emp B 20 20000 1
Emp G 12000 1
Emp A 10000 1
Emp C 20 28000 1
Emp D 5 30000 1
Emp E 10 32000 1
Emp F 5 20000 1

We can use the HAVING CLAUSE to remove the duplicate groups from the above fetched records as:

SELECT employee_name
,commission
,salary
,GROUP_ID()
FROM employee
GROUP BY commission
         ,salary
         ,employee_name
         ,ROLLUP(commission,salary)
HAVING GROUP_ID() < 1;

We will get the following output:

Employee_ID Commission Salary GROUP_ID
Emp H 12000 0
Emp B 20 20000 0
Emp G 12000 0
Emp A 10000 0
Emp C 20 28000 0
Emp D 5 30000 0
Emp E 10 32000 0
Emp F 5 20000 0

Here we can observe that we have fetched only unique group of records using HAVING in Oracle SQL / PLSQL SELECT statement.


Filed Under: function Tagged With: GROUPIDPLSQL, GROUP_ID function in oracle plsql, GROUP_ID function in oracle sql, how to use GROUP_ID function in oracle database query, how to use GROUP_ID function in oracle plsql, how to use GROUP_ID function in oracle sql, syntax and example of GROUP_ID function in oracle database query, syntax and example of GROUP_ID function in oracle plsql, syntax and example of GROUP_ID function in oracle sql, using GROUP_ID function in oracle database query, using GROUP_ID function in oracle plsql, using GROUP_ID function in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in