JOIN with aggregate functions in MySQL - Mini Project: Build & Apply

JOIN with aggregate functions

📖 Scenario: You are managing a small online bookstore database. You have two tables: books and sales. The books table stores book details, and the sales table records each sale of a book. You want to find out how many copies of each book have been sold in total.

🎯 Goal: Create a SQL query that joins the books and sales tables, then uses an aggregate function to calculate the total number of copies sold for each book.

📋 What You'll Learn

Create a books table with columns book_id (integer), title (string), and author (string).

Create a sales table with columns sale_id (integer), book_id (integer), and quantity (integer).

Write a SQL query that joins books and sales on book_id.

Use the SUM() aggregate function to calculate total copies sold per book.

Group the results by book_id and title.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use SQL joins and aggregate functions to analyze sales data and understand product performance.

💼 Career

Database analysts and developers often write queries combining joins and aggregates to generate reports and insights for decision-making.

1

Create the books table and insert data

Create a table called books with columns book_id (integer), title (varchar(100)), and author (varchar(100)). Insert these exact rows: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), (3, 'To Kill a Mockingbird', 'Harper Lee').

MySQL

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

Need a hint?

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

2

Create the sales table and insert data

Create a table called sales with columns sale_id (integer), book_id (integer), and quantity (integer). Insert these exact rows: (1, 1, 3), (2, 2, 5), (3, 1, 2), (4, 3, 4).

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (book_id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'To Kill a Mockingbird', 'Harper Lee');

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

Need a hint?

Use CREATE TABLE and INSERT INTO like in Step 1, but for the sales table.

3

Write a JOIN query with SUM aggregate

Write a SQL query that joins books and sales on book_id. Select books.book_id, books.title, and the total quantity sold as total_sold using the SUM() aggregate function. Group the results by books.book_id and books.title.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (book_id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE TABLE sales (
  sale_id INT,
  book_id INT,
  quantity INT
);

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 4);

-- Write the JOIN query with SUM aggregate
-- Your code here

Need a hint?

Use JOIN to combine tables and SUM() to add quantities. Don't forget GROUP BY.

4

Complete the query with ORDER BY

Add an ORDER BY clause to the query to sort the results by total_sold in descending order, so the book with the highest sales appears first.

MySQL

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (book_id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'To Kill a Mockingbird', 'Harper Lee');

CREATE TABLE sales (
  sale_id INT,
  book_id INT,
  quantity INT
);

INSERT INTO sales (sale_id, book_id, quantity) VALUES
(1, 1, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 4);

SELECT books.book_id, books.title, SUM(sales.quantity) AS total_sold
FROM books
JOIN sales ON books.book_id = sales.book_id
GROUP BY books.book_id, books.title
-- Add ORDER BY clause here

Need a hint?

Use ORDER BY total_sold DESC to sort from highest to lowest sales.