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 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  0 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 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 wor  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 wor  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 pre 800000  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 1610000 OPE  1320000  Sal  4936000  Sup  1015000  Eng  245000  Mgr  6246000  Pre  800000  TEC  115000  Wor 1475000  9 Rows selected.