Oracle PL/SQL group by Rollup

Rollup literally means to roll to, used in group by can play a cumulative sum role:

 

If no rollup exists, the following query sums the groups by department_id and job_id:

Select department_id, job_id, sum (salary) <br/> from employees <br/> where department_id <60 <br/> group by department_id, job_id;

Output:

Department_id, job_id, sum (salary) <br/> 50, st_clerk, 55700 <br/> 50, st_man, 36400 <br/> 30, pu_clerk, 13900 <br/> 50, sh_clerk, 64300 <br/> 20, mk_man, 13000 <br/> 30, pu_man, 11000 <br/> 10, ad_asst, 4400 <br/> 20, mk_rep, 6000 <br/> 40, hr_rep, 6500 <br/>

 

With rollup:

Group and sum department_id and job_id, sum them according to department_id, and finally calculate the sum:

Select department_id, job_id, sum (salary) <br/> from employees <br/> where department_id <60 <br/> group by rollup (department_id, job_id );

Output:

Department_id, job_id, sum (salary) <br/> 10, ad_asst, 4400 <br/> 10, 4400 <br/> 20, mk_man, 13000 <br/> 20, mk_rep, 6000 <br/> 20, 19000 <br/> 30, pu_man, 11000 <br/> 30, pu_clerk, 13900 <br/> 30 ,, 24900 <br/> 40, hr_rep, 6500 <br/> 40, 6500 <br/> 50, st_man, 36400 <br/> 50, sh_clerk, 64300 <br/> 50, st_clerk, 55700 <br/> 50, 156400 <br/>, 211200 <br/>

 

Group and sum the department_id and job_id, sum the sum according to job_id, and finally calculate the sum:

Select department_id, job_id, sum (salary) <br/> from employees <br/> where department_id <60 <br/> group by rollup (job_id, department_id );

Output:

Department_id, job_id, sum (salary) <br/> 40, hr_rep, 6500 <br/>, hr_rep, 6500 <br/> 20, mk_man, 13000 <br/>, mk_man, 13000 <br/> 20, mk_rep, 6000 <br/>, mk_rep, 6000 <br/> 30, pu_man, 11000 <br/>, pu_man, 11000 <br/> 50, st_man, 36400 <br/>, st_man, 36400 <br/> 10, ad_asst, 4400 <br/>, ad_asst, 4400 <br/> 30, pu_clerk, 13900 <br/>, pu_clerk, 13900 <br/> 50, sh_clerk, 64300 <br/>, sh_clerk, 64300 <br/> 50, st_clerk, 55700 <br/>, st_clerk, 55700 <br/>, 211200 <br/>

 

 

Ref: http://blog.csdn.net/zhaozhongju/archive/2009/05/13/4177358.aspx