Use 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.

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 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 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

When you use group by statements with Aggregate functions such as COUNT and SUM, you generally cannot get the total number of levels. In group by, each unique column combination generates a total number, but these total numbers are not "accumulated" into the total number at a higher level.
To achieve this, you can use group by rollup or group by cube to replace group by. However, they will generate all possible total numbers, but you may not need all of them. For group by cube, 2 ^ n groups are generated, where n is the number of columns in group.
View the following query, which uses the SH sample mode:
SELECT prod_id, cust_id, channel_id, SUM (quantity_sold)
FROM sales
WHERE cust_id <3
Group by cube (prod_id, cust_id, channel_id)
This will generate the total number of eight groups:
Total of all rows
Each channel, including all products and customers
Each customer, including all products and channels
Each product, including all customers and channels
Each channel/customer combination, including all products
Each channel/product portfolio, including all customers
Each product/customer combination, including all channels
Product, customer, and channel combinations
There may be many combinations. Each added column in the group by cube doubles the total number.
You can use group by grouping sets instead of group by cube. You can use the application to specify the total number of groups you are interested in. Because it does not have to calculate it and does not need a set (nor produce too many results), it is more efficient for the SQL engine.
The format is:
Group by grouping sets (list), (list )...)
Here (list) is a column sequence in parentheses. This combination generates a total number. To add a sum, you must add a (NUlL) grouping set.
For example, if you only need to generate the total number of each product (including all customers and channels) and each customer/channel combination (including all products), you can enter:
SELECT prod_id, cust_id, channel_id, SUM (quantity_sold)
FROM sales
WHERE cust_id <3
Group by grouping sets (
(Prod_id), (cust_id, channel_id)
);
This method reduces the total number of datasets generated from 180 to 37, and helps you focus on answering your questions.