GROUPING SETS for multiple groupings in PostgreSQL - Mini Project: Build & Apply

Using GROUPING SETS for Multiple Groupings in PostgreSQL

📖 Scenario: You are working as a data analyst for a retail company. The company wants to analyze sales data to understand revenue by different combinations of product categories and regions.

🎯 Goal: Build a PostgreSQL query using GROUPING SETS to get total sales grouped by category, by region, and by both category and region together.

📋 What You'll Learn

Create a table named sales with columns category (text), region (text), and amount (numeric).

Insert the exact sales data provided into the sales table.

Write a query using GROUPING SETS to group sales by category, by region, and by both category and region.

Include a column named total_sales that sums the amount for each grouping.

💡 Why This Matters

🌍 Real World

Retail companies often analyze sales data by different dimensions like product categories and regions to make better business decisions.

💼 Career

Data analysts and database developers use GROUPING SETS to efficiently generate multiple grouped summaries in a single query, saving time and resources.

1

Create the sales table and insert data

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

PostgreSQL

-- Create the sales table and insert data
-- Your code here

Need a hint?

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

2

Define the grouping sets for multiple groupings

Create a variable or comment named grouping_sets that lists the grouping sets: (category), (region), and (category, region).

PostgreSQL

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

INSERT INTO sales (category, region, amount) VALUES
('Electronics', 'North', 100),
('Electronics', 'South', 150),
('Clothing', 'North', 200),
('Clothing', 'South', 250);

-- Define grouping sets for category, region, and category with region
-- Your code here

Need a hint?

List the grouping sets exactly as (category), (region), and (category, region).

3

Write the query using GROUPING SETS

Write a SELECT query on sales that uses GROUP BY GROUPING SETS with the sets (category), (region), and (category, region). Select category, region, and sum of amount as total_sales.

PostgreSQL

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

INSERT INTO sales (category, region, amount) VALUES
('Electronics', 'North', 100),
('Electronics', 'South', 150),
('Clothing', 'North', 200),
('Clothing', 'South', 250);

-- Write the query using GROUPING SETS
SELECT category, region, SUM(amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((category), (region), (category, region));

Need a hint?

Use GROUP BY GROUPING SETS with the exact grouping sets and select the sum of amount as total_sales.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort results by category ascending and then by region ascending.

PostgreSQL

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

INSERT INTO sales (category, region, amount) VALUES
('Electronics', 'North', 100),
('Electronics', 'South', 150),
('Clothing', 'North', 200),
('Clothing', 'South', 250);

-- Add ORDER BY to sort by category and region
SELECT category, region, SUM(amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((category), (region), (category, region))
ORDER BY category ASC, region ASC;

Need a hint?

Use ORDER BY category ASC, region ASC to sort the results.