GROUP BY single and multiple columns in PostgreSQL - Mini Project: Build & Apply

GROUP BY Single and Multiple Columns in PostgreSQL

📖 Scenario: You are working with a sales database for a small bookstore. The database has a table called sales that records each book sale with the book's title, the genre, and the number of copies sold.Your task is to learn how to group sales data by single and multiple columns to find total copies sold per book and per genre.

🎯 Goal: Build SQL queries using GROUP BY to calculate total copies sold grouped by a single column (title) and by multiple columns (genre and title).

📋 What You'll Learn

Create a sales table with columns title (text), genre (text), and copies_sold (integer).

Insert specific sales data into the sales table.

Write a SQL query to group total copies sold by title.

Write a SQL query to group total copies sold by both genre and title.

💡 Why This Matters

🌍 Real World

Grouping sales data helps businesses understand which books or genres sell best, aiding inventory and marketing decisions.

💼 Career

Database professionals often write GROUP BY queries to summarize and analyze data for reports and dashboards.

1

Create the sales table and insert data

Create a table called sales with columns title (text), genre (text), and copies_sold (integer). Then insert these exact rows into the sales table:
('The Hobbit', 'Fantasy', 10), ('The Hobbit', 'Fantasy', 5), ('1984', 'Dystopian', 8), ('1984', 'Dystopian', 7), ('Dune', 'Science Fiction', 12).

PostgreSQL

-- Create the sales table and insert data here

Need a hint?

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

2

Write a query to group total copies sold by title

Write a SQL query that selects title and the sum of copies_sold as total_copies from the sales table. Use GROUP BY title to group the results by the title column.

PostgreSQL

CREATE TABLE sales (
    title TEXT,
    genre TEXT,
    copies_sold INTEGER
);

INSERT INTO sales (title, genre, copies_sold) VALUES
('The Hobbit', 'Fantasy', 10),
('The Hobbit', 'Fantasy', 5),
('1984', 'Dystopian', 8),
('1984', 'Dystopian', 7),
('Dune', 'Science Fiction', 12);

-- Write a query to group total copies sold by title
-- Your code here

Need a hint?

Use SUM() to add copies sold and GROUP BY title to group by book title.

3

Write a query to group total copies sold by genre and title

Write a SQL query that selects genre, title, and the sum of copies_sold as total_copies from the sales table. Use GROUP BY genre, title to group the results by both genre and title columns.

PostgreSQL

CREATE TABLE sales (
    title TEXT,
    genre TEXT,
    copies_sold INTEGER
);

INSERT INTO sales (title, genre, copies_sold) VALUES
('The Hobbit', 'Fantasy', 10),
('The Hobbit', 'Fantasy', 5),
('1984', 'Dystopian', 8),
('1984', 'Dystopian', 7),
('Dune', 'Science Fiction', 12);

SELECT title, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY title;

-- Write a query to group total copies sold by genre and title
-- Your code here

Need a hint?

Use GROUP BY genre, title to group by both columns.

4

Add an ORDER BY clause to sort the grouped results

Add an ORDER BY clause to the query that groups by genre and title to sort the results by genre ascending and then by total_copies descending.

PostgreSQL

CREATE TABLE sales (
    title TEXT,
    genre TEXT,
    copies_sold INTEGER
);

INSERT INTO sales (title, genre, copies_sold) VALUES
('The Hobbit', 'Fantasy', 10),
('The Hobbit', 'Fantasy', 5),
('1984', 'Dystopian', 8),
('1984', 'Dystopian', 7),
('Dune', 'Science Fiction', 12);

SELECT title, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY title;

SELECT genre, title, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY genre, title
-- Add ORDER BY clause here
-- Your code here

Need a hint?

Use ORDER BY genre ASC, total_copies DESC to sort by genre and then by total copies sold.