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:

1. SELECT department_id, job_id, SUM (salary)
2. FROM employees
3. WHERE department_id <60
4. Group by department_id, job_id;

Output:

1. DEPARTMENT_ID, JOB_ID, SUM (SALARY)
2. 50, ST_CLERK, 55700
3. 50, ST_MAN, 36400
4. 30, PU_CLERK, 13900
5. 50, SH_CLERK, 64300
6. 20, MK_MAN, 13000
7. Thirty, PU_MAN, 11000
8. 10, AD_ASST, 4400
9. 20, MK_REP, 6000
10. 40, HR_REP, 6500

With ROLLUP:

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

1. SELECT department_id, job_id, SUM (salary)
2. FROM employees
3. WHERE department_id <60
4. Group by rollup (department_id, job_id );

Output:

1. DEPARTMENT_ID, JOB_ID, SUM (SALARY)
2. 10, AD_ASST, 4400
3. 10, 4400
4. 20, MK_MAN, 13000
5. 20, MK_REP, 6000
6. 20, 19000
7. Thirty, PU_MAN, 11000
8. 30, PU_CLERK, 13900
9. 30, 24900
10. 40, HR_REP, 6500
11. 40, 6500
12. 50, ST_MAN, 36400
13. 50, SH_CLERK, 64300
14. 50, ST_CLERK, 55700
15. 50, 156400
16. , 211200

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

1. SELECT department_id, job_id, SUM (salary)
2. FROM employees
3. WHERE department_id <60
4. Group by rollup (job_id, department_id );

Output:

1. DEPARTMENT_ID, JOB_ID, SUM (SALARY)
2. 40, HR_REP, 6500
3. HR_REP, 6500
4. 20, MK_MAN, 13000
5. , MK_MAN, 13000
6. 20, MK_REP, 6000
7. , MK_REP 6000
8. Thirty, PU_MAN, 11000
9. , PU_MAN, 11000
10. 50, ST_MAN, 36400
11. , ST_MAN, 36400
12. 10, AD_ASST, 4400
13. AD_ASST, 4400
14. 30, PU_CLERK, 13900
15. , PU_CLERK, 13900
16. 50, SH_CLERK, 64300
17. , SH_CLERK, 64300
18. 50, ST_CLERK, 55700
19. , ST_CLERK, 55700
20. , 211200