7.7 use the rollup clause

-- Pass a column to Rollup
Select division_id, sum (salary) from employees2 group by rollup (division_id );
-- Pass multiple columns to Rollup
Select division_id, job_id, sum (salary) from employees2 group by rollup (division_id, job_id );
-- Modify the position of the column passed to Rollup
Select job_id, division_id, sum (salary) from employees2 group by rollup (job_id, division_id );

-- Pass a column to the Cube
Select division_id, sum (salary) from employees2 group by cube (division_id );
-- Pass multiple columns to cube
Select division_id, job_id, sum (salary) from employees2 group by cube (division_id, job_id );
-- Modify the position of the column passed to the Cube
Select job_id, division_id, sum (salary) from employees2 group by cube (job_id, division_id );

-- Grouping if the column value is null, 1 is returned. If the column value is not empty, 0 is returned.
Select grouping (division_id), division_id, sum (salary) from employees2 group by rollup (division_id );
-- Use decode to convert the value of a grouping column to Rollup
Select decode (grouping (division_id), 1, 'all divisions ', division_id) Div, division_id, sum (salary) from employees2 group by rollup (division_id );
-- Use decode to convert the value of multiple grouping columns to Rollup
Select decode (grouping (division_id), 1, 'all divisions', division_id) Div,
Decode (grouping (job_id), 1, 'all jobs', job_id) Job,
Division_id,
Sum (salary)
From employees2
Group by rollup (division_id, job_id );

-- Use decode to convert the value cube of a grouping Column
Select decode (grouping (division_id), 1, 'all divisions ', division_id) Div, division_id, sum (salary) from employees2 group by cube (division_id );
-- Use decode to convert the value cube of multiple grouping Columns
Select decode (grouping (division_id), 1, 'all divisions', division_id) Div,
Decode (grouping (job_id), 1, 'all jobs', job_id) Job,
Division_id,
Sum (salary)
From employees2
Group by cube (division_id, job_id );

-- Use the grouping sets clause to return only the subtotal record
Select division_id, job_id, sum (salary) from employees2 group by grouping sets (division_id, job_id );

-- Grouping_id
Select division_id,
Job_id,
Grouping_id (division_id) div_grp,
Grouping_id (job_id) job_grp,
Grouping_id (division_id, job_id) grp_id,
Sum (salary)
From employees2
Group by cube (division_id, job_id );

-- Use having to filter
Select division_id,
Job_id,
Grouping_id (division_id, job_id) grp_id,
Sum (salary)
From employees2
Group by cube (division_id, job_id)
Having grouping_id (division_id, job_id)> 0;

--
Select division_id, job_id, sum (salary)
From employees2
Group by division_id, rollup (division_id, job_id );

-- Group_id eliminates Repeated Records returned by the Group by clause
Select division_id, job_id, group_id (), sum (salary)
From employees2
Group by division_id, rollup (division_id, job_id );

Select division_id, job_id, group_id (), sum (salary)
From employees2
Group by division_id, rollup (division_id, job_id) having group_id () = 0;

7.7 use the rollup clause