Oracle Group by clause along with ROLLUP or CUBE operators

Use the ROLLUP OPERATOR to produce subtotal values, ROLLUP IS AN EXTENSION OF GROUP BY CLAUSE, Use the CUBE OPERATOR to produce cross-tabulation values.  Use the GROUPING function to identify the row values created by ROLLUP or CUBE.  These operators and GROUPING function can best be used ALONG WITH GROUP FUNCTIONS, as group functions operate on a set of rows to give one result per group.

• Examples of Rollup:

          1. SELECT e.department_id, SUM(e.salary)

                       FROM employees e

                                   WHERE e.department_id < 30 

                                                 GROUP BY ROLLUP(e.department_id);

             Results:

          2. SELECT e.department_id, e.job_id, SUM(e.salary) 

                         FROM employees e

                                     WHERE e.department_id < 30 

                                                    GROUP BY ROLLUP(e.department_id, e.job_id);
              Results: 

•    Example of 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.

       1. SELECT e.department_id, SUM(e.salary) 

                          FROM employees e

                                      WHERE e.department_id < 30 GROUP BY CUBE(e.department_id);
           Results:

      2. The following query produces subtotaling results based on job, based on deptno and based on the                 individual jobs(clerk or analyst or manager etc in dept 10 and 20)  

          SELECT e.department_id, e.job_id, SUM(e.salary)
                         FROM employees e
                                     WHERE e.department_id < 30 GROUP BY CUBE(e.department_id, e.job_id);

          Results: