Practice GROUP BY, ROLLUP and CUBE - Exercise

Contact Us or call 1-877-932-8228
Practice GROUP BY, ROLLUP and CUBE - Exercise

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;
Next