GROUP BY clause in MySQL - Mini Project: Build & Apply

Using GROUP BY Clause in MySQL

📖 Scenario: You work for a small bookstore that wants to analyze sales data.The store keeps a record of each sale with the book title and the number of copies sold.

🎯 Goal: Create a MySQL query that groups sales by book title and calculates the total copies sold for each book.

📋 What You'll Learn

Create a table called sales with columns book_title (VARCHAR) and copies_sold (INT).

Insert the exact sales data given.

Write a query that uses GROUP BY book_title to sum copies_sold for each book.

Order the results by total copies sold in descending order.

💡 Why This Matters

🌍 Real World

Grouping and summarizing sales data helps businesses understand which products sell best.

💼 Career

Database analysts and developers use GROUP BY queries to generate reports and insights from data.

1

Create the sales table and insert data

Create a table called sales with columns book_title as VARCHAR(100) and copies_sold as INT. Then insert these exact rows: ('The Alchemist', 5), ('The Alchemist', 3), ('1984', 7), ('1984', 2), ('Brave New World', 4).

MySQL

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

Need a hint?

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

2

Add a variable for ordering direction

Create a variable called order_direction and set it to 'DESC' to specify descending order for the query results.

MySQL

CREATE TABLE sales (
  book_title VARCHAR(100),
  copies_sold INT
);

INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 5),
('The Alchemist', 3),
('1984', 7),
('1984', 2),
('Brave New World', 4);

-- Create a variable order_direction set to 'DESC'
-- Your code here

Need a hint?

Use SET @order_direction = 'DESC'; to create a user variable in MySQL.

3

Write the GROUP BY query to sum copies sold

Write a SELECT query that selects book_title and the sum of copies_sold as total_copies from sales. Use GROUP BY book_title to group the results by book title.

MySQL

CREATE TABLE sales (
  book_title VARCHAR(100),
  copies_sold INT
);

INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 5),
('The Alchemist', 3),
('1984', 7),
('1984', 2),
('Brave New World', 4);

SET @order_direction = 'DESC';

-- Write a SELECT query with GROUP BY book_title and SUM(copies_sold) as total_copies
-- Your code here

Need a hint?

Use SUM(copies_sold) to add up copies sold per book.

4

Order the grouped results by total copies sold

Add an ORDER BY clause to the previous query to order the results by total_copies using the variable @order_direction for the direction.

MySQL

CREATE TABLE sales (
  book_title VARCHAR(100),
  copies_sold INT
);

INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 5),
('The Alchemist', 3),
('1984', 7),
('1984', 2),
('Brave New World', 4);

SET @order_direction = 'DESC';

SELECT book_title, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY book_title
-- Add ORDER BY total_copies using @order_direction
-- Your code here

Need a hint?

Use ORDER BY total_copies DESC to sort from highest to lowest.