ROLLUP
Use the ROLLUP operator to Produce the sub total values,
ROLLUP is an Extension GROUP BY clause
SELECT Department_id, SUM (salary)
FROM Employees
WHERE department_id IN (10,20,30)
GROUP BY ROLLUP(Department_id);
- - - - - - - - - - - - - - - - - - - - -
Department_id SUM(salary)
- - - - - - - - - - - - - - - - - - - - -
10 4400
20 19000
30 24900
48300
- - - - - - - - - - - - - - - - - - - - -
SELECT 4400+19000+24900 FROM dual;
48300
SELECT Department_id "Dep_id", job_id, SUM (salary)
FROM Employees
WHERE department_id IN (90,20,30)
GROUP BY ROLLUP(Department_id, Job_id);
- - - - - - - - - - - - - - - - - -
Dep_id Job_id SUM(salary)
- - - - - - - - - - - - - - - - - -
20 MK_MAN 13000
20 MK_REP 6000
20 19000
30 PU_MAN 11000
30 PU_CLERK 13900
30 24900
90 AD_VP 34000
90 AD_PRES 24000
90 58000
101900
- - - - - - - - - - - - - - - - - -
CUBE
The cube operator is used to produce results sets that are typically used for cross-tabular reports.
This means Rollup produces only one possible subtotaling where as Cube produces subtotal for all possible conditions of grouping specified in the group by clause and a grand total
SELECT Department_id "Dept_id", SUM (salary)
FROM Employees
WHERE department_id IN (10,20,30)
GROUP BY CUBE(Department_id);
- - - - - - - - - - - -
Dept_id SUM(salary)
- - - - - - - - - - - -
48300
10 4400
20 19000
30 24900
- - - - - - - - - - - -
The following query produces subtotaling results based on job,based on deptno and based on the individual jobs(MK_MAN or MK_REP or PU_MAN or AD_ASST or PU_CLERK etc in dept 10, 20 and 30)
SELECT Department_id "Dept_id", job_id, SUM (salary)
FROM Employees
WHERE department_id IN (10,20,30)
GROUP BY CUBE(Department_id, job_id );
- - - - - - - - - - - - - - - - - - - -
Dept_id Job_id SUM(salary)
- - - - - - - - - - - - - - - - - - - -
48300
MK_MAN 13000
MK_REP 6000
PU_MAN 11000
AD_ASST 4400
PU_CLERK 13900
10 4400
10 AD_ASST 4400
20 19000
20 MK_MAN 13000
20 MK_REP 6000
30 24900
30 PU_MAN 11000
30 PU_CLERK 13900
- - - - - - - - - - - - - - - - - - - -
