Oracle grouping_id Function

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.

 

For details about how to use the grouping function, refer to the article I wrote earlier.

Http://blog.csdn.net/wh62592855/archive/2009/11/16/4818072.aspx

 

1. grouping_id usage example

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 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 530000
Bus pre 0 0 800000
Bus wor 0 0 0 280000
Bus 0 1 1 1610000
OPE Eng 0 0 245000
OPE Mgr 0 0 805000
OPE wor 0 0 0 270000
OPE 0 1 1320000
Sal Mgr 0 0 4446000
Sal wor 0 0 490000
Sal 0 1 4936000

Div job div_grp job_grp grp_id sum (salary)
-----------------------------------------------
Sup Mgr 0 0 465000
Sup TEC 0 0 115000
Sup wor 0 0 435000
Sup 0 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 8881000

21 rows selected.

 

2. gourping bit vector computing

As shown in the preceding example:

Division_id job_id bit vector grouping_id () Return Value

Non-null 00 0

Non-empty 01 1

Null, not empty, 10 2

Null 11 3

 

3. Application of 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 1610000
OPE 1 1320000
Sal 1 4936000
Sup 1 1015000
Eng 2 245000
Mgr 2 6246000
Pre 2 800000
TEC 115000
Wor 2 1475000
3 8881000

10 rows selected.

 

4. Use one column multiple times in the group by clause

You can use a column in the group by clause multiple times to reorganize 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)
-----------------
Bus Mgr 530000
Bus pre 800000
Bus wor 280000
OPE Eng 245000
OPE Mgr 805000
OPE wor 270000
Sal Mgr 4446000
Sal wand 490000
Sup Mgr 465000
Sup TEC 115000
Sup wor 435000

Div job sum (salary)
-----------------
Bus 1, 1610000
OPE 1320000
Sal 1, 4936000
Sup 1015000
Bus 1, 1610000
OPE 1320000
Sal 4936000
Sup 1015000

19 rows selected.

 

However, note that the last four rows of records are repeated with the first four rows of records. This duplication can be eliminated by using group_id.

 

5. Use the group_id Function

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 above 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 530000
Bus pre 0 800000
Bus wor 0 280000
OPE Eng 0 245000
OPE Mgr 0 805000
OPE wor 0 270000
Sal Mgr 0 4446000
Sal wor 0 490000
Sup Mgr 0 465000
Sup TEC 0 115000
Sup wor 0 435000

Div job group_id () sum (salary)
---------------------------
Bus 0 1610000
OPE 0 1320000
Sal 0 4936000
Sup 0 1015000
Bus 1 1610000
OPE 1 1320000
Sal 1 4936000
Sup 1 1015000

19 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
3 group by division_id, rollup (division_id, job_id)
4 having group_id () = 0;

Div job group_id () sum (salary)
---------------------------
Bus Mgr 0 530000
Bus pre 0 800000
Bus wor 0 280000
OPE Eng 0 245000
OPE Mgr 0 805000
OPE wor 0 270000
Sal Mgr 0 4446000
Sal wor 0 490000
Sup Mgr 0 465000
Sup TEC 0 115000
Sup wor 0 435000

Div job group_id () sum (salary)
---------------------------
Bus 0 1610000
OPE 0 1320000
Sal 0 4936000
Sup 0 1015000

15 rows selected.