# Practice GROUP BY, ROLLUP and CUBE - Exercise

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;