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

Related examples in the same category

1.	An Example Query that Illustrates the Use of GROUPING_ID()
2.	You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BY clause
3.	Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0

One useful application of GROUPING_ID() is to filter rows using a HAVING clause : GROUPING_ID « Analytical Functions « Oracle PL / SQL

Related examples in the same category