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).

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 below the HR connection. Right click on EMP_DETAILS_VIEW and choose the Quick DDL > Save to Worksheet tab to see the Data Definition Language (DDL) used to create the view: EMP_DETAILS_VIEW

The view definition is as follows:

--------------------------------------------------------
--  DDL for View EMP_DETAILS_VIEW
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "C##HR"."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.

Here is a simple query using that view that includes two columns that are grouped with the count() function.

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;

This query shows the number of employees in each country: emp_details_view-q1

To create the same query without the view, you would have to join many tables:

SELECT r.region_name,
    c.country_name, 
    count(*)
FROM regions r
  JOIN countries c ON r.region_id = c.region_id
  JOIN locations l ON c.country_id = l.country_id
  JOIN departments d ON l.location_id = d.location_id
  JOIN employees e ON d.department_id = e.department_id
GROUP BY r.region_name, c.country_name
ORDER BY r.region_name, c.country_name;

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

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

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;

The query output is shown below: emp_details_view-rollup

GROUPING Function

The DECODE function provides conditional functionality similar to if/else logic in procedural programming languages. It will be explained in the following video.

The grouping() function returns a 0> (indicating a detail record) or a 1 (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 to display labels identifying a summary row.

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 results of the query are shown below: emp_details_view-grouping

Using Cube

Take a look at this query that uses rollup():

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: rollup() not cube()

Now, how we can use cube() to get both summaries:

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: cube() not rollup()

And see how we can further improve it with the grouping() and decode() functions:

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: cube() not rollup() with grouping

Practice GROUP BY, ROLLUP and CUBE

Duration: 25 to 40 minutes.
  1. Create a query that returns the count of employees by region, country, state and department.
  2. Add a rollup() function 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

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;