24 Using ROLLUP and CUBE Operations and GROUPING SETS

• ·         Used to create subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the clause-ROLLUP
• ·         An extension to the GROUP BY clause like ROLLUP that produces cross-tabulation reports-CUBE
• ·         Used to specify multiple groupings of data- GROUPING SETS

1.  Within the Employees table, each manager_id is the manager of one or more employees who each have a job_id and earn a salary. For each manager, what is the total salary earned by all of the employees within each job_id? Write a query to display the Manager_id, job_id, and total salary. Include in the result the subtotal salary for each manager and a grand total of all salaries.

SELECT  manager_id, job_id, SUM(salary) "total salary", GROUPING(manager_id), GROUPING(job_id)

FROM employees

GROUP BY ROLLUP(manager_id, job_id);

2.  Amend the previous query to also include a subtotal salary for each job_id regardless of the manager_id.

SELECT  manager_id, job_id, SUM(salary) "total salary", GROUPING(manager_id), GROUPING(job_id)

FROM employees

GROUP BY CUBE(manager_id, job_id);

Observe 12 new rows here:

3.  Using GROUPING SETS, write a query to show the following groupings:

• department_id, manager_id, job_id

• manager_id, job_id

• department_id, manager_id

SELECT department_id, manager_id, job_id, SUM(salary) "total salary",  GROUPING(department_id),  GROUPING(manager_id), GROUPING(job_id)

FROM employees

GROUP BY GROUPING SETS((department_id, manager_id, job_id), (manager_id, job_id), (department_id, manager_id));