Introduction to Oracle grouping_id group_id grouping

You can use the GROUPING_ID function to filter records with the HAVING clause, removing records that do not contain subtotal or total. The GROUPING_ID () function can accept one or more columns and return the decimal value of the GROUPING bit vector. The calculation method of the GROUPING bit vector is to combine the results of calling the GROUPING function for each column in sequence. 1. GROUPING_ID usage instance 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) www.2cto.com 7 from employees2 8 group by cube (division_id, job_id) 9 order by division_id, job_id; div job DIV_GRP JOB_GRP GRP_ID SUM (SALARY) --- ---------- ----------- bus mgr 0 0 0 530000BUS PRE 0 0 0 800000BUS WOR 0 0 0 0 281_bus 0 1 1 161_ope ENG 0 0 0 0 245000OPE MGR 0 0 0 0 805000OPE WOR 0 0 0 0 271_ope 0 1 1 1320000SAL MGR 0 0 0 4446000SAL WOR 0 0 0 491_sal 0 1 1 4936000DIV JOB DIV_GRP JOB_GRP GRP_ID SUM (SALARY) --- ---------- ----------- sup mgr 0 0 0 0000000sup TEC 0 0 0 115000SUP WOR 0 0 0 0 435000SUP 0 1 1 1015000 ENG 1 0 2 245000 MGR 1 0 2 6246000 PRE 1 0 2 800000 TEC 1 0 2 115000 WOR 1 0 2 1475000 1 1 3 888100021 rows selected. www.2cto.com 2. GOURPING bit vector calculation
As shown in the preceding example, the division_id job_id bit vector GROUPING_ID () returned value is non-empty, non-empty, 00 0, non-empty, 01, 1, null, 10, 2, empty, 11, 3, and GROUPING_ID ().
GROUPING_ID () is used to filter records using HAVING clauses. The HAVING clause can remove records that do not contain subtotal or a total. You only need to check the return value of GROUPING_ID () to see if it is greater than zero. SQL> select 2 division_id, job_id, 3 grouping_id (division_id, job_id) as grp_id, 4 sum (salary) 5 from employees2 6 group by cube (division_id, job_id) 7 having grouping_id (division_id, job_id)> 0 8 order by division_id, job_id; div job GRP_ID SUM (SALARY) --- ---------- ----------- BUS 1 1620.ope 1 1320000SAL 1 4936000SUP 1 1015000 ENG 2 245000 MGR 2 6246000 PRE 2 800000 TEC 2 115000 WOR 2 1475000 3 8 88100010 rows selected. 4. You can use a column multiple times in the group by clause to re-organize data, or perform statistics based on different data groups. For example, the following query contains a group by clause, which uses the division_id column twice. The first is to GROUP division_id and the second is to use it in ROLLUP. SQL> select division_id, job_id, sum (salary) 2 from employees2 3 group by division_id, rollup (division_id, job_id); DIV JOB SUM (SALARY) capacity --- ----------- bus mgr before PRE 800000BUS WOR 281_ope ENG 245000OPE MGR 805000OPE WOR 271_sal MGR 4446000SAL wor mgr has TEC has WOR has job sum (SALARY) --- ----------- BUS 161_ope 1320000SAL has been released US 16.0ope 1320000SAL 4936000SUP 101500019 rows selected. Note that the last four rows of records are the same as those of the previous four rows. This duplication can be eliminated by using GROUP_ID. 5. The GROUP_ID function can be used to eliminate repeated records returned BY the group by clause. GROUP_ID () does not accept any parameters. If a specific group appears n times, GROUP_ID () returns an integer from 0 to n-1. Next we will rewrite the preceding example SQL> select division_id, job_id, group_id (), sum (salary) 2 from employees2 3 group by division_id, rollup (division_id, job_id ); div job GROUP_ID () SUM (SALARY) --- ---------- ----------- bus mgr 0 530000BUS PRE 0 800000BUS WOR 0 281_ope ENG 0 245000OPE MGR 0 805000OPE WOR 0 271_sal MGR 0 4446000SAL WOR 0 491_sup MGR 0 1_tec 0 1_wor 0 limit JOB GROUP_ID () SUM (SALARY) --- ---------- ----------- BUS 0 161_ope 0 1320000SAL 0 4936000SUP 0 1015000BUS 1 161_ope 1 1320000SAL 1 4936000SUP 1 101500019 rows selected.
You can use the HAVING clause to eliminate repeated records. Only records whose GROUP_ID () is equal to 0 are returned.
SQL> select division_id, job_id, group_id (), sum (salary) 2 from employees2 www.2cto.com 3 group by division_id, rollup (division_id, job_id) 4 having group_id () = 0; div job GROUP_ID () SUM (SALARY) --- ---------- ----------- bus mgr 0 530000BUS PRE 0 800000BUS WOR 0 281_ope ENG 0 245000OPE MGR 0 805000OPE WOR 0 271_sal MGR 0 4446000SAL WOR 0 491_sup MGR 0 1_tec 0 1_wor 0 limit JOB GROUP_ID () SUM (SALARY) --- ---------- ----------- BUS 0 161_ope 0 1320000SAL 0 4936000SUP 0 101500015 rows selected. author runming918