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

Need a 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

Need a 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

Need a 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

Need a hint?

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