facebook google plus twitter
Webucator's Free Advanced Oracle SQL Queries Tutorial

Lesson: Rollup & Cube

Welcome to our free Advanced Oracle SQL Queries tutorial. This tutorial is based on Webucator's Advanced Oracle SQL Queries course.

In this lesson, you will learn about enhanced group processing and using rollup and cube.

Lesson Goals

  • Learn about enhanced group processing.
  • Learn about using rollup.
  • Learn about using cube.

About Group Processing

The GROUP BY statement is used in conjunction with analytic functions. The grouped rows are used to compute an aggregate value (such as a count, sum or average).

EMP_DETAILS_VIEW

The Employee Details View (EMP_DETAILS_VIEW) is included as part of the HR Schema. This view provides inner joins between the employees, departments, jobs, locations, countries, and regions tables.

The definition can be seen in SQLDeveloper by Navigating in the Connections panel on the left side to your connection and opening the Views folder. Right click on EMP_DETAILS_VIEW and choose the SQL tab to see the DDL used to create the view.

The view definition is as follows:

CREATE OR REPLACE VIEW EMP_DETAILS_VIEW (EMPLOYEE_ID, JOB_ID, MANAGER_ID, DEPARTMENT_ID, 
LOCATION_ID, COUNTRY_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT, DEPARTMENT_NAME, 
JOB_TITLE, CITY, STATE_PROVINCE, COUNTRY_NAME, REGION_NAME) AS 
SELECT
	e.employee_id,
	e.job_id,
	e.manager_id,
	e.department_id,
	d.location_id,
	l.country_id,
	e.first_name,
	e.last_name,
	e.salary,
	e.commission_pct,
	d.department_name,
	j.job_title,
	l.city,
	l.state_province,
	c.country_name,
	r.region_name
FROM
	employees e,
	departments d,
	jobs j,
	locations l,
	countries c,
	regions r
WHERE e.department_id = d.department_id
	AND d.location_id = l.location_id
	AND l.country_id = c.country_id
	AND c.region_id = r.region_id
	AND j.job_id = e.job_id
WITH READ ONLY;

Note: the join syntax is the old Oracle form rather than ANSI standard.

Simple GROUP BY

Code Sample:

Enhancing-Groups-With-Rollup-and-Cube/Demos/group_by.sql
SELECT 
	region_name, 
	country_name,  
	count(*) 
FROM emp_details_view 
GROUP BY region_name, country_name
ORDER BY region_name, country_name;

The initial query includes two columns that are grouped using the count analytic function.

ROLLUP Function

Code Sample:

Enhancing-Groups-With-Rollup-and-Cube/Demos/roll_up.sql
SELECT 
	region_name, 
	country_name,  
	count(*) 
FROM emp_details_view 
GROUP BY ROLLUP(region_name, country_name)
ORDER BY region_name, country_name;

By simply adding the ROLLUP function in the group by clause - totals are generated at the county and region levels.

GROUPING Function

The DECODE function provides conditional functionality (like if/else logic in procedural programming languages. It will be covered in depth in the chapter on conditional processing).

Code Sample:

Enhancing-Groups-With-Rollup-and-Cube/Demos/grouping.sql
SELECT 
	decode(grouping(region_name),0,region_name,'GRAND') AS region_name,
	decode(grouping(country_name),0,country_name,'TOTAL') AS country_name,  
	count(*) 
FROM emp_details_view 
GROUP BY ROLLUP(region_name, country_name);

The grouping function returns a zero (indicating a detail record) or a one (indicating a summary row). Oracle calls the summary rows superaggregate rows to distinguish them from regular grouped rows. The GROUPING function can be used with a DECODE statement (which provides if/else like functionality) to display labels identifying a summary row.

Using Cube

Code Sample:

Enhancing-Groups-With-Rollup-and-Cube/Demos/roll_up2.sql
SELECT 
region_name, first_name,  count(*) 
FROM emp_details_view 
WHERE first_name LIKE 'Da%'
GROUP BY rollup(region_name, first_name);

Although we see the summaries by region in this query, we do not see a summary by first name.

Code Sample:

Enhancing-Groups-With-Rollup-and-Cube/Demos/cube.sql
SELECT 
	region_name, 
	first_name,  
	count(*) 
FROM emp_details_view 
WHERE first_name LIKE 'Da%'
GROUP BY cube(region_name, first_name);

By using CUBE rather than ROLLUP, summaries at both the region and first name level are returned.

Code Sample:

Enhancing-Groups-With-Rollup-and-Cube/Demos/cube2.sql
SELECT 
	decode(grouping(region_name),0,region_name,'SUMMARY') AS region_name, 
	decode(grouping(first_name),0,first_name,'TOTAL') AS first_name, 
	count(*)
FROM emp_details_view 
WHERE first_name LIKE 'Da%'
GROUP BY CUBE(region_name, first_name);

This query uses the GROUPING and DECODE functions to provide labels for the total lines.

Practice GROUP BY, ROLLUP and CUBE

Duration: 15 to 25 minutes.
  1. Create a query that returns the count of employees by region, country, state and department.
  2. Add a ROLLUP clause to the query and examine the results.
  3. Add calls to the GROUPING function for each level.

Solution:

Enhancing-Groups-With-Rollup-and-Cube/Solutions/aggregates_solution.sql
--
-- Start by getting a count of employees by region, country, state and 
-- department
--
SELECT 
	region_name, 
	country_name, 
	state_province, 
	department_name, 
	count(*) 
FROM emp_details_view 
GROUP BY region_name, country_name, state_province, department_name
ORDER BY region_name, country_name, state_province, department_name;

--
-- Recall that all columns that appear in the SELECT clause 
-- (other than the analytic function) must also appear in the 
-- GROUP BY clause.
--
-- The following version includes a ROLLUP in the GROUP BY clause
--
SELECT 	
	region_name, 
	country_name, 
	state_province, 
	department_name, 
	count(*) 
FROM emp_details_view 
GROUP BY ROLLUP(region_name, country_name, state_province, department_name)
ORDER BY region_name, country_name, state_province, department_name;

--
-- Add a grouping function call for each level of grouping 
-- represented in the query.
--
SELECT 
	grouping (region_name) AS g1, 
	grouping (country_name) AS g2, 
	grouping (state_province) AS g3, 
	grouping (department_name) AS g4,
	region_name, 
	country_name, 
	state_province, 
	department_name, 
	count(*) 
FROM emp_details_view 
GROUP BY ROLLUP(
	region_name, 
	country_name, 
	state_province, 
	department_name
)
ORDER BY region_name, country_name, state_province, department_name;
	
-- This provides a subtotal for each grouping level.

Challenge

  1. Limit the results to only display the detail, region_name and grand totals levels.

Challenge Solution:

Enhancing-Groups-With-Rollup-and-Cube/Solutions/aggregates_challenge.sql
--
-- Recall, each grouping row either contains a 1 
-- (indicating that it is a summary row)or a 0 
-- (indicating that it is not a summary row).  
--
-- To simplify the next query, we can concatenate the grouping values 
-- returned into a single value.  This is not necessary, but makes selecting
-- summary lines from queries with multiple levels of grouping simpler.
--
SELECT 
	grouping (region_name) || 
	grouping (country_name) ||  
	grouping (state_province) ||  
	grouping (department_name) AS group_level,
	region_name, 
	country_name, 
	state_province, 
	department_name, 
	count(*) 
FROM emp_details_view 
GROUP BY ROLLUP (
	region_name, 
	country_name, 
	state_province, 
	department_name
)
ORDER BY region_name, country_name, state_province;

--
-- Now we can move the grouping calls to the having clause and choose 
-- only the levels that are required.  The grouping call needs to 
-- execute after the group by clause so it must appear in the having 
-- clause (rather than the where clause).
--
SELECT 
	region_name, 
	country_name, 
	state_province, 
	department_name, 
	count(*) 
FROM emp_details_view 
GROUP BY ROLLUP(
	region_name, 
	country_name, 
	state_province, 
	department_name
)
HAVING 	
	grouping (region_name) || 
	grouping (country_name) ||  
	grouping (state_province) ||  
	grouping (department_name)
IN (
		'0000', -- a regular row
		'0111', -- a summary record at the region_name level  
		'1111'  -- a grand total
)
ORDER BY region_name, country_name, state_province;