ROLLUP and CUBE for hierarchical totals in PostgreSQL - Mini Project: Build & Apply

Using ROLLUP and CUBE for Hierarchical Totals in PostgreSQL

📖 Scenario: You work for a retail company that tracks sales data by region, store, and product category. Your manager wants to see sales totals at different levels: by product category alone, by store and product category, by region and product category, and overall totals.

🎯 Goal: Build SQL queries using ROLLUP and CUBE to calculate hierarchical sales totals for the company.

📋 What You'll Learn

Create a table called sales with columns region, store, category, and amount.

Insert sample sales data with at least 6 rows covering multiple regions, stores, and categories.

Write a query using GROUP BY ROLLUP(region, store, category) to get hierarchical totals.

Write a query using GROUP BY CUBE(region, store, category) to get all combinations of totals.

💡 Why This Matters

🌍 Real World

Retail and sales companies often need to see totals at different levels, such as by store, region, or product category. ROLLUP and CUBE help create these summaries easily.

💼 Career

Understanding how to write SQL queries with ROLLUP and CUBE is useful for data analysts and database developers who prepare reports and dashboards.

1

Create the sales table and insert sample data

Create a table called sales with columns region (text), store (text), category (text), and amount (numeric). Then insert these exact rows into sales: ('North', 'StoreA', 'Electronics', 100), ('North', 'StoreA', 'Clothing', 150), ('North', 'StoreB', 'Electronics', 200), ('South', 'StoreC', 'Clothing', 300), ('South', 'StoreC', 'Electronics', 250), ('South', 'StoreD', 'Clothing', 100).

PostgreSQL

-- Create the sales table
-- Insert the sample rows

Hint

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the data.

2

Set up a query to use ROLLUP for hierarchical totals

Write a SQL query that selects region, store, category, and the sum of amount as total_sales from sales. Use GROUP BY ROLLUP(region, store, category) to get hierarchical totals. Order the results by region, store, and category.

PostgreSQL

CREATE TABLE sales (
    region TEXT,
    store TEXT,
    category TEXT,
    amount NUMERIC
);

INSERT INTO sales (region, store, category, amount) VALUES
('North', 'StoreA', 'Electronics', 100),
('North', 'StoreA', 'Clothing', 150),
('North', 'StoreB', 'Electronics', 200),
('South', 'StoreC', 'Clothing', 300),
('South', 'StoreC', 'Electronics', 250),
('South', 'StoreD', 'Clothing', 100);

-- Write a query using GROUP BY ROLLUP(region, store, category) to get hierarchical totals

Hint

Use GROUP BY ROLLUP(region, store, category) to get totals at each level: category, store, region, and grand total.

3

Set up a query to use CUBE for all combinations of totals

Write a SQL query that selects region, store, category, and the sum of amount as total_sales from sales. Use GROUP BY CUBE(region, store, category) to get totals for all combinations of these columns. Order the results by region, store, and category.

PostgreSQL

CREATE TABLE sales (
    region TEXT,
    store TEXT,
    category TEXT,
    amount NUMERIC
);

INSERT INTO sales (region, store, category, amount) VALUES
('North', 'StoreA', 'Electronics', 100),
('North', 'StoreA', 'Clothing', 150),
('North', 'StoreB', 'Electronics', 200),
('South', 'StoreC', 'Clothing', 300),
('South', 'StoreC', 'Electronics', 250),
('South', 'StoreD', 'Clothing', 100);

SELECT region, store, category, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(region, store, category)
ORDER BY region, store, category;

-- Write a query using GROUP BY CUBE(region, store, category) to get all combinations of totals

Hint

Use GROUP BY CUBE(region, store, category) to get totals for every combination of the three columns.

4

Add a final filter to exclude rows where all grouping columns are NULL

Modify the previous CUBE query to exclude the row where region, store, and category are all NULL. Add a HAVING clause after GROUP BY that keeps only rows where at least one of region, store, or category is NOT NULL.

PostgreSQL

CREATE TABLE sales (
    region TEXT,
    store TEXT,
    category TEXT,
    amount NUMERIC
);

INSERT INTO sales (region, store, category, amount) VALUES
('North', 'StoreA', 'Electronics', 100),
('North', 'StoreA', 'Clothing', 150),
('North', 'StoreB', 'Electronics', 200),
('South', 'StoreC', 'Clothing', 300),
('South', 'StoreC', 'Electronics', 250),
('South', 'StoreD', 'Clothing', 100);

-- Modify the CUBE query to exclude rows where region, store, and category are all NULL

Hint

Add a HAVING clause after the GROUP BY to filter out the row where all grouping columns are NULL.

Practice

1. What does the ROLLUP operator do in a PostgreSQL GROUP BY clause?

easy

A. It filters rows based on a condition before grouping.

B. It creates hierarchical subtotals from left to right for the grouped columns.

C. It generates all possible combinations of grouped columns for totals.

D. It sorts the result set after grouping.

Solution

1. Step 1: Understand ROLLUP purpose

   ROLLUP creates subtotals that roll up from the first column to the last in the GROUP BY list.

2. Step 2: Compare with other options

   CUBE creates all combinations, filtering and sorting are unrelated to ROLLUP.

3. Final Answer:

   It creates hierarchical subtotals from left to right for the grouped columns. -> Option B

4. Quick Check:

   ROLLUP = hierarchical subtotals [OK]

Hint: ROLLUP = subtotals in order of columns [OK]

Common Mistakes:

• Confusing ROLLUP with CUBE
• Thinking ROLLUP filters rows
• Assuming ROLLUP sorts results

2. Which of the following is the correct syntax to use CUBE in a PostgreSQL query?

easy

A. SELECT col1, col2, SUM(sales) FROM table CUBE BY (col1, col2);

B. SELECT col1, col2, SUM(sales) FROM table GROUP BY CUBE col1, col2;

C. SELECT col1, col2, SUM(sales) FROM table GROUP BY ROLLUP(col1, col2);

D. SELECT col1, col2, SUM(sales) FROM table GROUP BY CUBE(col1, col2);

Solution

1. Step 1: Recall correct GROUP BY syntax with CUBE

   The correct syntax is GROUP BY CUBE(col1, col2) with parentheses around columns.

2. Step 2: Identify incorrect options

   SELECT col1, col2, SUM(sales) FROM table CUBE BY (col1, col2); uses invalid 'CUBE BY', C uses ROLLUP, D misses parentheses.

3. Final Answer:

   SELECT col1, col2, SUM(sales) FROM table GROUP BY CUBE(col1, col2); -> Option D

4. Quick Check:

   GROUP BY CUBE(columns) syntax [OK]

Hint: Use GROUP BY CUBE(col1, col2) with parentheses [OK]

Common Mistakes:

• Omitting parentheses in CUBE
• Using 'CUBE BY' instead of 'GROUP BY CUBE'
• Confusing ROLLUP and CUBE syntax

3. Given the table sales with columns region, product, and amount, what will be the output rows count of this query?

SELECT region, product, SUM(amount) FROM sales GROUP BY ROLLUP(region, product);

medium

A. Number of distinct (region, product) pairs plus one subtotal per region plus one grand total row.

B. Number of distinct (region, product) pairs only.

C. Number of distinct regions only.

D. Number of distinct products only.

Solution

1. Step 1: Understand ROLLUP output rows

   ROLLUP(region, product) returns all (region, product) groups, plus subtotals per region, plus a grand total row.

2. Step 2: Count rows

   Total rows = distinct (region, product) + distinct regions (subtotals) + 1 (grand total).

3. Final Answer:

   Number of distinct (region, product) pairs plus one subtotal per region plus one grand total row. -> Option A

4. Quick Check:

   ROLLUP adds subtotals and grand total [OK]

Hint: ROLLUP adds subtotals and grand total rows [OK]

Common Mistakes:

• Ignoring subtotal and grand total rows
• Counting only distinct pairs
• Confusing ROLLUP with CUBE output

4. Identify the error in this query using CUBE:

SELECT region, product, SUM(amount) FROM sales GROUP BY CUBE region, product;

medium

A. GROUP BY cannot use CUBE with more than one column.

B. SUM(amount) cannot be used with CUBE.

C. Missing parentheses around columns in CUBE function.

D. region and product must be in ORDER BY, not GROUP BY.

Solution

1. Step 1: Check CUBE syntax

   CUBE requires parentheses around the columns: CUBE(region, product).

2. Step 2: Validate other parts

   SUM(amount) is valid, multiple columns allowed, ORDER BY is unrelated.

3. Final Answer:

   Missing parentheses around columns in CUBE function. -> Option C

4. Quick Check:

   CUBE syntax needs parentheses [OK]

Hint: Always use parentheses with CUBE columns [OK]

Common Mistakes:

• Omitting parentheses in CUBE
• Thinking SUM is invalid with CUBE
• Confusing GROUP BY and ORDER BY

5. You want to generate a report showing sales totals by region, product, and year, including all subtotals and grand totals. Which query correctly uses CUBE to achieve this?

hard

A. SELECT region, product, year, SUM(amount) FROM sales GROUP BY CUBE(region, product, year);

B. SELECT region, product, year, SUM(amount) FROM sales GROUP BY region, product, year;

C. SELECT region, product, year, SUM(amount) FROM sales GROUP BY GROUPING SETS(region, product, year);

D. SELECT region, product, year, SUM(amount) FROM sales GROUP BY ROLLUP(region, product, year);

Solution

1. Step 1: Understand the requirement

   All subtotals and grand totals for region, product, and year means all combinations of these columns.

2. Step 2: Choose correct grouping method

   CUBE generates all combinations, ROLLUP only hierarchical totals, GROUPING SETS needs explicit sets, simple GROUP BY no subtotals.

3. Final Answer:

   SELECT region, product, year, SUM(amount) FROM sales GROUP BY CUBE(region, product, year); -> Option A

4. Quick Check:

   CUBE = all combinations subtotals [OK]

Hint: Use CUBE for all subtotal combinations [OK]

Common Mistakes:

• Using ROLLUP instead of CUBE for all subtotals
• Confusing GROUPING SETS with CUBE
• Forgetting subtotals with simple GROUP BY