GROUP BY multiple columns in SQL - Mini Project: Build & Apply

GROUP BY Multiple Columns in SQL

📖 Scenario: You work for a small bookstore that wants to analyze its sales data. The store keeps records of each sale, including the book title, the genre, and the number of copies sold. Your manager wants to see how many copies were sold for each combination of book title and genre.

🎯 Goal: Create an SQL query that groups sales data by both book_title and genre columns, and calculates the total copies sold for each group.

📋 What You'll Learn

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

Insert the exact sales data provided into the sales table.

Write a query that groups the data by book_title and genre.

Calculate the sum of copies_sold for each group.

💡 Why This Matters

🌍 Real World

Grouping sales data by multiple columns helps businesses understand combined effects, like how many copies of each book in each genre sold.

💼 Career

SQL GROUP BY with multiple columns is a common skill for data analysts and database developers to summarize and report data effectively.

1

Create the sales table and insert data

Create a table called sales with columns book_title (text), genre (text), and copies_sold (integer). Then insert these exact rows into sales: ('The Alchemist', 'Fiction', 5), ('The Alchemist', 'Adventure', 3), ('Deep Work', 'Self-help', 7), ('Deep Work', 'Productivity', 4), ('The Alchemist', 'Fiction', 2).

SQL

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

Need a hint?

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

2

Set up the SELECT statement with columns

Write a SELECT statement that selects book_title and genre from the sales table. Do not add GROUP BY or aggregation yet.

SQL

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

INSERT INTO sales (book_title, genre, copies_sold) VALUES
('The Alchemist', 'Fiction', 5),
('The Alchemist', 'Adventure', 3),
('Deep Work', 'Self-help', 7),
('Deep Work', 'Productivity', 4),
('The Alchemist', 'Fiction', 2);

-- Write SELECT statement for book_title and genre
SELECT book_title, genre
FROM sales;

Need a hint?

Use SELECT book_title, genre FROM sales to see the columns.

3

Add GROUP BY for book_title and genre

Modify the SELECT statement to group the results by both book_title and genre using GROUP BY book_title, genre.

SQL

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

INSERT INTO sales (book_title, genre, copies_sold) VALUES
('The Alchemist', 'Fiction', 5),
('The Alchemist', 'Adventure', 3),
('Deep Work', 'Self-help', 7),
('Deep Work', 'Productivity', 4),
('The Alchemist', 'Fiction', 2);

-- Add GROUP BY for book_title and genre
SELECT book_title, genre
FROM sales
GROUP BY book_title, genre;

Need a hint?

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

4

Calculate total copies sold per group

Add a column to the SELECT statement that calculates the sum of copies_sold for each group. Use SUM(copies_sold) AS total_copies and keep the GROUP BY book_title, genre.

SQL

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

INSERT INTO sales (book_title, genre, copies_sold) VALUES
('The Alchemist', 'Fiction', 5),
('The Alchemist', 'Adventure', 3),
('Deep Work', 'Self-help', 7),
('Deep Work', 'Productivity', 4),
('The Alchemist', 'Fiction', 2);

-- Add SUM aggregation for copies_sold
SELECT book_title, genre, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY book_title, genre;

Need a hint?

Use SUM(copies_sold) AS total_copies in SELECT and keep GROUP BY book_title, genre.