Using Group by clause along with ROLLUP or CUBE operators

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

- - - - - - - - - - - - - - - - - - - -