Types of Queries - CUBE, ROLLUP, GROUPING

The CUBE, ROLLUP and GROUPING SETS functions are used in the GROUP BY clause to generate totals and subtotals.

Example : ROLLUP(DEPARTMENT_ID) will give a department wise sub total and total across all departments.

Rollup

Along with aggregation on a particular column say department, rollup also gives the grand total of all these aggregations eg - department wise aggregation and additionally aggregation for all the departments. Thus rollup is an extension of the GROUP BY CLAUSE.

SELECT DEPARTMENT_ID, SUM(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10,20,50)
GROUP BY ROLLUP(DEPARTMENT_ID);

DEPARTMENT_ID	SUM(SALARY)
50	157000
20	19000
10	4400
	180400

CUBE

CUBE is a tabulated summary for row data. It calculates aggregates like subtotals, count, max values etc for all possible combinations of groups mentioned in the CUBE clause. It also calculates a final total across all the groups. This information is useful in reports where aggregates are required across groups.

Example

In the Employee table we want the select Departwise and Job Wise max salary and the employee name for each aggregate. 

Here the combination of groups would be

Department wise
Job Wise
Department and Job Wise.

This can be achieved in a single query using the CUBE functions.

SELECT max(FIRST_NAME)
KEEP (DENSE_RANK FIRST ORDER BY SALARY DESC) FIRST_NAME,
DEPARTMENT_ID,
JOB_ID,
MAX(SALARY)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
AND DEPARTMENT_ID IN (10,20,50)
GROUP BY
CUBE(DEPARTMENT_ID, JOB_ID);

FIRST_NAME	DEPARTMENT_ID	JOB_ID	MAX(SALARY)
Michael			13000
Michael		MK_MAN	13000
Pat		MK_REP	6000
Payam		ST_MAN	8200
Jennifer		AD_ASST	4400
Nandita		SH_CLERK	4200
Renske		ST_CLERK	3600
Jennifer	10		4400
Jennifer	10	AD_ASST	4400
Michael	20		13000
Michael	20	MK_MAN	13000
Pat	20	MK_REP	6000
Payam	50		8200
Payam	50	ST_MAN	8200
Nandita	50	SH_CLERK	4200
Renske	50	ST_CLERK	3600

Grouping Set

SELECT max(FIRST_NAME)
SELECT
 CASE WHEN GROUPING(FIRST_NAME) = 0 THEN NULL
      WHEN GROUP_ID() = 0 THEN 'SUM'
      WHEN GROUP_ID() = 1 THEN 'AVG'
 END TYPE,
 FIRST_NAME,
 DEPARTMENT_ID,
 CASE WHEN GROUPING(FIRST_NAME) = 0 THEN NULL
      WHEN GROUP_ID() = 0 THEN ROUND(SUM(SALARY),2)
      WHEN GROUP_ID() = 1 THEN ROUND(AVG(SALARY),2)
 END VALUE
FROM
 EMPLOYEES
WHERE DEPARTMENT_ID IN (10,20,90) 
GROUP BY
 GROUPING SETS
 (
  (FIRST_NAME, DEPARTMENT_ID),
  (DEPARTMENT_ID),
  (DEPARTMENT_ID),
  (),
  ()
 )
 ORDER BY DEPARTMENT_ID, FIRST_NAME, GROUP_ID();  

TYPE	FIRST_NAME	DEPARTMENT_ID	VALUE
	Jennifer	10
SUM		10	4400
AVG		10	4400
	Michael	20
	Pat	20
SUM		20	19000
AVG		20	9500
	Lex	90
	Neena	90
	Steven	90
SUM		90	58000
AVG		90	19333.33
SUM			81400
AVG			13566.67

For more details and some good examples on Rollup, Cube and Grouping Sets you can refer to the book by Laurent Schneider, link of which is given below

Advance Oracle SQL Programming : Laurent Schneider 

Thought for the day

God has a bigger plan for me than I have for myself

--Anonymous

God's Word for the day

For surely I know the plans I have for you, says the Lord, 

 plans for your welfare and not for harm, 

To give you a future with hope. 

Then when you will call on me and come and pray to me, 

  I will hear you.

Jeremiah 29:11-12