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

GROUP BY with multiple columns in MySQL

📖 Scenario: You work for a small bookstore that wants to analyze its sales data. The store records each sale with the book's genre, the month of sale, and the number of copies sold.The manager wants to see the total copies sold grouped by both genre and month to understand which genres sell best in which months.

🎯 Goal: Create a MySQL query that groups sales data by both genre and month, and calculates the total copies sold for each group.

📋 What You'll Learn

Create a table called book_sales with columns genre (VARCHAR), month (VARCHAR), and copies_sold (INT).

Insert the exact sales data provided into the book_sales table.

Write a SELECT query that groups the data by genre and month.

Calculate the total copies sold for each group using SUM(copies_sold).

Order the results by genre ascending and month ascending.

💡 Why This Matters

🌍 Real World

Grouping sales data by multiple columns helps businesses analyze trends across different categories and time periods.

💼 Career

Understanding GROUP BY with multiple columns is essential for data analysis roles, report generation, and database querying in many jobs.

1

Create the book_sales table and insert data

Create a table called book_sales with columns genre as VARCHAR(20), month as VARCHAR(10), and copies_sold as INT. Then insert these exact rows into book_sales: ('Fiction', 'January', 120), ('Fiction', 'February', 100), ('Non-Fiction', 'January', 80), ('Non-Fiction', 'February', 90), ('Science', 'January', 50), ('Science', 'February', 60).

MySQL

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

Need a hint?

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

2

Set up the SELECT statement with grouping columns

Start writing a SELECT query that selects genre and month from the book_sales table. Add a GROUP BY clause that groups by both genre and month.

MySQL

CREATE TABLE book_sales (
  genre VARCHAR(20),
  month VARCHAR(10),
  copies_sold INT
);

INSERT INTO book_sales (genre, month, copies_sold) VALUES
('Fiction', 'January', 120),
('Fiction', 'February', 100),
('Non-Fiction', 'January', 80),
('Non-Fiction', 'February', 90),
('Science', 'January', 50),
('Science', 'February', 60);

-- Write SELECT genre, month and GROUP BY genre, month
-- Your code here

Need a hint?

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

3

Add the aggregation to calculate total copies sold

Modify the SELECT query to include SUM(copies_sold) AS total_copies to calculate the total copies sold for each group of genre and month.

MySQL

CREATE TABLE book_sales (
  genre VARCHAR(20),
  month VARCHAR(10),
  copies_sold INT
);

INSERT INTO book_sales (genre, month, copies_sold) VALUES
('Fiction', 'January', 120),
('Fiction', 'February', 100),
('Non-Fiction', 'January', 80),
('Non-Fiction', 'February', 90),
('Science', 'January', 50),
('Science', 'February', 60);

SELECT genre, month, SUM(copies_sold) AS total_copies
FROM book_sales
GROUP BY genre, month;
-- Your code here

Need a hint?

Use SUM(copies_sold) AS total_copies to get the total copies sold per group.

4

Order the results by genre and month

Add an ORDER BY clause to the SELECT query to sort the results by genre ascending and then by month ascending.

MySQL

CREATE TABLE book_sales (
  genre VARCHAR(20),
  month VARCHAR(10),
  copies_sold INT
);

INSERT INTO book_sales (genre, month, copies_sold) VALUES
('Fiction', 'January', 120),
('Fiction', 'February', 100),
('Non-Fiction', 'January', 80),
('Non-Fiction', 'February', 90),
('Science', 'January', 50),
('Science', 'February', 60);

SELECT genre, month, SUM(copies_sold) AS total_copies
FROM book_sales
GROUP BY genre, month
ORDER BY genre ASC, month ASC;
-- Your code here

Need a hint?

Use ORDER BY genre ASC, month ASC to sort the results.