Oracle PL/SQL/Analytical Functions/GROUPING ID

An Example Query that Illustrates the Use of GROUPING_ID()

SQL> CREATE TABLE employee(
  2    employee_id INTEGER,
  3    division_id CHAR(3),
  4    job_id CHAR(3),
  5    first_name VARCHAR2(10) NOT NULL,
  6    last_name VARCHAR2(10) NOT NULL,
  7    salary NUMBER(6, 0)
  8  );
Table created.
SQL>
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(1, "BUS","PRE","James","Smith","800000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(2, "SAL","MGR","Ron","Johnson","350000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(3, "SAL","WOR","Fred","Hobbs","140000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(4, "SUP","MGR","Susan","Jones","200000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(5, "SAL","WOR","Rob","Green","350000");
1 row created.
SQL>
SQL> select * from employee;
EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME      SALARY
----------- --- --- ---------- ---------- ----------
          1 BUS PRE James      Smith          800000
          2 SAL MGR Ron        Johnson        350000
          3 SAL WOR Fred       Hobbs          140000
          4 SUP MGR Susan      Jones          200000
          5 SAL WOR Rob        Green          350000
SQL>
SQL>
SQL>
SQL>
SQL> --An Example Query that Illustrates the Use of GROUPING_ID()
SQL>
SQL> --The following example passes division_id and job_id to GROUPING_ID(). 
Notice the output from the GROUPING_ID() function agrees with the expected returned 
values documented in the previous section:
SQL>
SQL> SELECT
  2   division_id, job_id,
  3   GROUPING(division_id) AS DIV_GRP,
  4   GROUPING(job_id) AS JOB_GRP,
  5   GROUPING_ID(division_id, job_id) AS grp_id,
  6   SUM(salary)
  7  FROM employee
  8  GROUP BY CUBE(division_id, job_id);
DIV JOB    DIV_GRP    JOB_GRP     GRP_ID SUM(SALARY)
--- --- ---------- ---------- ---------- -----------
                 1          1          3     1840000
    MGR          1          0          2      550000
    PRE          1          0          2      800000
    WOR          1          0          2      490000
BUS              0          1          1      800000
BUS PRE          0          0          0      800000
SAL              0          1          1      840000
SAL MGR          0          0          0      350000
SAL WOR          0          0          0      490000
SUP              0          1          1      200000
SUP MGR          0          0          0      200000
11 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>

Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0

SQL> CREATE TABLE employee(
  2    employee_id INTEGER,
  3    division_id CHAR(3),
  4    job_id CHAR(3),
  5    first_name VARCHAR2(10) NOT NULL,
  6    last_name VARCHAR2(10) NOT NULL,
  7    salary NUMBER(6, 0)
  8  );
Table created.
SQL>
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(1, "BUS","PRE","James","Smith","800000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(2, "SAL","MGR","Ron","Johnson","350000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(3, "SAL","WOR","Fred","Hobbs","140000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(4, "SUP","MGR","Susan","Jones","200000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(5, "SAL","WOR","Rob","Green","350000");
1 row created.
SQL>
SQL> select * from employee;
EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME      SALARY
----------- --- --- ---------- ---------- ----------
          1 BUS PRE James      Smith          800000
          2 SAL MGR Ron        Johnson        350000
          3 SAL WOR Fred       Hobbs          140000
          4 SUP MGR Susan      Jones          200000
          5 SAL WOR Rob        Green          350000
SQL>
SQL>
SQL> --Eliminate duplicate rows using a HAVING clause that only allows rows whose 
GROUP_ID() is 0
SQL>
SQL> SELECT division_id, job_id, GROUP_ID(), SUM(salary)
  2  FROM employee
  3  GROUP BY division_id, ROLLUP(division_id, job_id)
  4  HAVING GROUP_ID() = 0;
DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS PRE          0      800000
SAL MGR          0      350000
SAL WOR          0      490000
SUP MGR          0      200000
BUS              0      800000
SAL              0      840000
SUP              0      200000
7 rows selected.
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>

One useful application of GROUPING_ID() is to filter rows using a HAVING clause

SQL> CREATE TABLE employee(
  2    employee_id INTEGER,
  3    division_id CHAR(3),
  4    job_id CHAR(3),
  5    first_name VARCHAR2(10) NOT NULL,
  6    last_name VARCHAR2(10) NOT NULL,
  7    salary NUMBER(6, 0)
  8  );
Table created.
SQL>
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(1, "BUS","PRE","James","Smith","800000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(2, "SAL","MGR","Ron","Johnson","350000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(3, "SAL","WOR","Fred","Hobbs","140000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(4, "SUP","MGR","Susan","Jones","200000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(5, "SAL","WOR","Rob","Green","350000");
1 row created.
SQL>
SQL> select * from employee;
EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME      SALARY
----------- --- --- ---------- ---------- ----------
          1 BUS PRE James      Smith          800000
          2 SAL MGR Ron        Johnson        350000
          3 SAL WOR Fred       Hobbs          140000
          4 SUP MGR Susan      Jones          200000
          5 SAL WOR Rob        Green          350000
SQL>
SQL>
SQL>
SQL>
SQL> --A Useful Application of GROUPING_ID()
SQL>
SQL> --One useful application of GROUPING_ID() is to filter rows using a HAVING clause. 
Your HAVING clause can exclude rows that don�t contain a subtotal or total by simply checking 
if GROUPING_ID() returns a value greater than 0. For example:
SQL>
SQL> SELECT
  2   division_id, job_id,
  3   GROUPING_ID(division_id, job_id) AS grp_id,
  4   SUM(salary)
  5  FROM employee
  6  GROUP BY CUBE(division_id, job_id)
  7  HAVING GROUPING_ID(division_id, job_id) > 0;
DIV JOB     GRP_ID SUM(SALARY)
--- --- ---------- -----------
                 3     1840000
    MGR          2      550000
    PRE          2      800000
    WOR          2      490000
BUS              1      800000
SAL              1      840000
SUP              1      200000
7 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> drop table employee;

You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BY clause

SQL> CREATE TABLE employee(
  2    employee_id INTEGER,
  3    division_id CHAR(3),
  4    job_id CHAR(3),
  5    first_name VARCHAR2(10) NOT NULL,
  6    last_name VARCHAR2(10) NOT NULL,
  7    salary NUMBER(6, 0)
  8  );
Table created.
SQL>
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(1, "BUS","PRE","James","Smith","800000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(2, "SAL","MGR","Ron","Johnson","350000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(3, "SAL","WOR","Fred","Hobbs","140000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(4, "SUP","MGR","Susan","Jones","200000");
1 row created.
SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
  2                 values(5, "SAL","WOR","Rob","Green","350000");
1 row created.
SQL>
SQL> select * from employee;
EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME      SALARY
----------- --- --- ---------- ---------- ----------
          1 BUS PRE James      Smith          800000
          2 SAL MGR Ron        Johnson        350000
          3 SAL WOR Fred       Hobbs          140000
          4 SUP MGR Susan      Jones          200000
          5 SAL WOR Rob        Green          350000
SQL>
SQL> --Using the GROUP_ID() Function
SQL>
SQL> --You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BY clause. 
GROUP_ID() doesn�t accept any parameters. 
If n duplicates exist for a particular grouping, GROUP_ID returns numbers in the range 0 to n-1.
SQL>
SQL>
SQL> SELECT division_id, job_id, GROUP_ID(), SUM(salary)
  2  FROM employee
  3  GROUP BY division_id, ROLLUP(division_id, job_id);
DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS PRE          0      800000
SAL MGR          0      350000
SAL WOR          0      490000
SUP MGR          0      200000
BUS              0      800000
SAL              0      840000
SUP              0      200000
BUS              1      800000
SAL              1      840000
SUP              1      200000
10 rows selected.
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>