Usage of Oracle grouping Functions)

The grouping function can accept a column and returns 0 or 1. If the column value is empty, grouping () returns 1; if the column value is not empty, returns 0. Grouping can only be used in queries using rollup or cube. Grouping () is useful when a value needs to be displayed in the return of a null value.

For more information about the use of rollup and cube functions, see my other article.

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

1. Use grouping () for a single column in Rollup ()

SQL> select division_id, sum (salary)
2 from employees2
3 group by rollup (division_id)
4 order by division_id;

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

Add grouping.

SQL> select grouping (division_id), division_id, sum (salary)
2 from employees2
3 group by rollup (division_id)
4 order by division_id;

Grouping (division_id) Div sum (salary)
-----------------------------------
0 bus 1610000
0 ope 1320000
Zero Sal 4936000
0 sup 1015000
1 8881000
It can be seen that 1 is returned for a blank location, and 0 is returned for a non-empty location.

2. Use Case to convert the return value of grouping ()

Maybe you will think that the previous 0 and 1 are too boring to represent any meaning. To put it bluntly, it is not human-friendly. At this time, we can use case to convert to some meaningful values.

SQL> select
2 case grouping (division_id)
3 when 1 then 'all divisions'
4 else division_id
5 end as Div,
6 sum (salary)
7 from employees2
8 group by rollup (division_id)
9 order by division_id;

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

3. Use Case and grouping () to convert values of multiple columns

SQL> select
2 case grouping (division_id)
3 when 1 then 'all divisions'
4 else division_id
5 end as Div,
6 case grouping (job_id)
7 when 1 then 'all jobs'
8 else job_id
9 end as job,
10 sum (salary)
11 from employees2
12 Group by rollup (division_id, job_id)
13 order by division_id, job_id;

Div job sum (salary)
--------------------------------
Bus Mgr 530000
Bus pre 800000
Bus wor 280000
Bus all jobs 1610000
OPE Eng 245000
OPE Mgr 805000
OPE wor 270000
OPE all jobs 1320000
Sal Mgr 4446000
Sal wand 490000
Sal all jobs 4936000

Div job sum (salary)
--------------------------------
Sup Mgr 465000
Sup TEC 115000
Sup wor 435000
Sup all jobs 1015000
All divisions all jobs 8881000

16 rows selected.

4. Use cube with grouping ()

SQL> select
2 case grouping (division_id)
3 when 1 then 'all divisions'
4 else division_id
5 end as Div,
6 case grouping (job_id)
7 when 1 then 'all jobs'
8 else job_id
9 end as job,
10 sum (salary)
11 from employees2
12 Group by cube (division_id, job_id)
13 order by division_id, job_id;

Div job sum (salary)
--------------------------------
Bus Mgr 530000
Bus pre 800000
Bus wor 280000
Bus all jobs 1610000
OPE Eng 245000
OPE Mgr 805000
OPE wor 270000
OPE all jobs 1320000
Sal Mgr 4446000
Sal wand 490000
Sal all jobs 4936000

Div job sum (salary)
--------------------------------
Sup Mgr 465000
Sup TEC 115000
Sup wor 435000
Sup all jobs 1015000
All divisions Eng 245000
All divisions Mgr 6246000.
All divisions pre800000
All divisions TEC 115000
All divisions wor 1475000
All divisions all jobs 8881000

21 rows selected.

5. Use the grouping sets clause

You can use the grouping sets clause to return only the subtotal record.

SQL> select division_id, job_id, sum (salary)
2 from employees2
3 group by grouping sets (division_id, job_id)
4 order by division_id, job_id;

Div job sum (salary)
-----------------
Bus 1, 1610000
OPE 1320000
Sal 1, 4936000
Sup 1015000
Eng 245000
Mgr 1, 6246000
Pre800000
TEC 115000
Wor 1475000

9 rows selected.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/wh62592855/archive/2009/11/16/4818072.aspx