Mega Code Archive
An Example Query that Illustrates the Use of GROUPING_ID()
<![CDATA[
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>
]]>