Mega Code Archive

 

Categories / Oracle PLSQL / Analytical Functions

 

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;