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

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

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

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

Hint

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

Practice

1. What does the following SQL query do?
SELECT department, COUNT(employee_id) FROM employees JOIN departments ON employees.dept_id = departments.id GROUP BY department;

easy

A. Counts employees in each department by joining employees and departments tables.

B. Lists all employees without grouping by department.

C. Calculates the average salary per department.

D. Deletes employees who are not assigned to any department.

Solution

1. Step 1: Understand the JOIN operation

   The query joins the employees and departments tables using the department ID to combine related data.

2. Step 2: Analyze the GROUP BY and COUNT

   It groups the results by department and counts the number of employees in each group.

3. Final Answer:

   Counts employees in each department by joining employees and departments tables. -> Option A

4. Quick Check:

   JOIN + GROUP BY + COUNT = Employee count per department [OK]

Hint: JOIN tables, GROUP BY key, then apply COUNT [OK]

Common Mistakes:

• Ignoring GROUP BY causes wrong aggregation
• Using COUNT without JOIN returns incomplete data
• Confusing COUNT with SUM or AVG

2. Which of the following is the correct syntax to get the total sales per customer using JOIN and SUM?
SELECT customers.name, SUM(orders.amount) FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name;

easy

A. SELECT customers.name, SUM(orders.amount) FROM customers LEFT JOIN orders GROUP BY customers.name;

B. SELECT customers.name, SUM(orders.amount) FROM customers JOIN orders WHERE customers.id = orders.customer_id GROUP BY customers.name;

C. SELECT customers.name, SUM(orders.amount) FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name;

D. SELECT customers.name, SUM(orders.amount) FROM customers, orders GROUP BY customers.name;

Solution

1. Step 1: Check JOIN syntax

   SELECT customers.name, SUM(orders.amount) FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name; uses correct INNER JOIN syntax with ON clause to link customers and orders.

2. Step 2: Verify GROUP BY and SUM usage

   SELECT customers.name, SUM(orders.amount) FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name; correctly groups by customer name and sums order amounts.

3. Final Answer:

   SELECT customers.name, SUM(orders.amount) FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name; -> Option C

4. Quick Check:

   JOIN with ON + GROUP BY + SUM = Correct syntax [OK]

Hint: Use JOIN ... ON ... with GROUP BY for aggregates [OK]

Common Mistakes:

• Using WHERE instead of ON in JOIN
• Missing ON clause causes syntax error
• Grouping by wrong column

3. Given tables:
employees(id, name, dept_id)
departments(id, department)
What is the output of this query?

SELECT d.department, COUNT(e.id) AS emp_count FROM employees e LEFT JOIN departments d ON e.dept_id = d.id GROUP BY d.department ORDER BY emp_count DESC;

medium

A. Syntax error due to missing GROUP BY column.

B. List of departments with employee counts, including departments with zero employees.

C. List of employees with their department names.

D. List of departments with employee counts, excluding departments with zero employees.

Solution

1. Step 1: Understand LEFT JOIN effect

   FROM employees e LEFT JOIN departments d ON e.dept_id = d.id includes all employees and matching departments if they exist. Departments with no employees do not appear because no employee row matches them.

2. Step 2: GROUP BY and COUNT behavior

   Grouping by d.department counts employees only for departments that have at least one employee (count >=1). Departments with zero employees are excluded.

3. Final Answer:

   List of departments with employee counts, excluding departments with zero employees. -> Option D

4. Quick Check:

   LEFT JOIN from employees + GROUP BY department excludes zero counts [OK]

Hint: LEFT JOIN keeps all left rows, COUNT counts matches [OK]

Common Mistakes:

• Assuming INNER JOIN behavior excludes zero counts
• Confusing COUNT(*) with COUNT(column)
• Ignoring NULL values in grouping

4. Identify the error in this query:

SELECT d.department, COUNT(e.id) FROM employees e JOIN departments d ON e.dept_id = d.id WHERE e.salary > 50000 GROUP BY e.dept_id;

medium

A. GROUP BY should use d.department instead of e.dept_id.

B. Missing HAVING clause for salary filter.

C. COUNT(e.id) cannot be used with JOIN.

D. ON clause is incorrect syntax.

Solution

1. Step 1: Check GROUP BY columns

   The SELECT lists d.department but GROUP BY uses e.dept_id, causing mismatch.

2. Step 2: Fix GROUP BY to match SELECT

   GROUP BY should use d.department or include it to avoid errors.

3. Final Answer:

   GROUP BY should use d.department instead of e.dept_id. -> Option A

4. Quick Check:

   GROUP BY columns must match SELECT non-aggregates [OK]

Hint: GROUP BY columns must match SELECT non-aggregates [OK]

Common Mistakes:

• Grouping by wrong or missing columns
• Using WHERE instead of HAVING for aggregates
• Incorrect JOIN ON syntax

5. You have tables:
orders(order_id, customer_id, amount)
customers(customer_id, name)
Write a query to find customers who have total orders amount greater than 1000, including customers with no orders showing zero total.
Which query is correct?

hard

A. SELECT c.name, SUM(o.amount) AS total FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE total > 1000 GROUP BY c.name;

B. SELECT c.name, COALESCE(SUM(o.amount), 0) AS total FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name HAVING total > 1000;

C. SELECT c.name, SUM(o.amount) AS total FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name HAVING total > 1000;

D. SELECT c.name, SUM(o.amount) AS total FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name WHERE total > 1000;

Solution

1. Step 1: Use LEFT JOIN to include customers without orders

   LEFT JOIN ensures all customers appear, even if no matching orders exist.

2. Step 2: Use COALESCE to convert NULL sums to zero and HAVING to filter totals

   COALESCE replaces NULL with 0; HAVING filters groups by aggregate result.

3. Final Answer:

   SELECT c.name, COALESCE(SUM(o.amount), 0) AS total FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name HAVING total > 1000; -> Option B

4. Quick Check:

   LEFT JOIN + COALESCE + HAVING filters totals correctly [OK]

Hint: Use LEFT JOIN + COALESCE + HAVING for conditional aggregates [OK]

Common Mistakes:

• Using WHERE instead of HAVING for aggregate filters
• Ignoring NULL sums for customers without orders
• Using INNER JOIN excludes customers with zero orders