Why aggregation summarizes data in MySQL - See It in Action

Why Aggregation Summarizes Data

📖 Scenario: You work at a small bookstore. You have a table that records each sale with the book title and the number of copies sold. You want to understand the total sales for each book to see which books sell the most.

🎯 Goal: Build a simple database query that uses aggregation to summarize total sales per book.

📋 What You'll Learn

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

Insert these exact rows into sales: ('The Hobbit', 3), ('1984', 5), ('The Hobbit', 2), ('Dune', 4), ('1984', 1).

Write a query that uses GROUP BY on book_title and SUM(copies_sold) to find total copies sold per book.

Order the results by total copies sold in descending order.

💡 Why This Matters

🌍 Real World

Aggregations help businesses quickly understand total sales, averages, or counts from many records, making it easier to make decisions.

💼 Career

Database aggregation queries are essential skills for data analysts, business intelligence developers, and anyone working with data to summarize and report information.

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 rows exactly: ('The Hobbit', 3), ('1984', 5), ('The Hobbit', 2), ('Dune', 4), ('1984', 1).

MySQL

-- Create the sales table and insert the given rows
-- Your code here

Need a hint?

Use CREATE TABLE to make the table. Use INSERT INTO sales (book_title, copies_sold) VALUES (...) to add rows.

2

Add a variable for sorting order

Create a variable called sort_order and set it to 'DESC' to use later for ordering the results.

MySQL

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

INSERT INTO sales (book_title, copies_sold) VALUES
('The Hobbit', 3),
('1984', 5),
('The Hobbit', 2),
('Dune', 4),
('1984', 1);

-- Create a variable called sort_order and set it to 'DESC'
-- Your code here

Need a hint?

Use SET @sort_order = 'DESC'; to create a variable for sorting order.

3

Write the aggregation query

Write a query that selects book_title and the sum of copies_sold as total_sold. Use GROUP BY book_title to group sales by book.

MySQL

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

INSERT INTO sales (book_title, copies_sold) VALUES
('The Hobbit', 3),
('1984', 5),
('The Hobbit', 2),
('Dune', 4),
('1984', 1);

SET @sort_order = 'DESC';

-- Write a query to select book_title and SUM(copies_sold) as total_sold grouped by book_title
-- Your code here

Need a hint?

Use SELECT book_title, SUM(copies_sold) AS total_sold FROM sales GROUP BY book_title; to summarize sales.

4

Order the results by total sales

Add an ORDER BY clause to the query to sort the results by total_sold in descending order using the variable @sort_order.

MySQL

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

INSERT INTO sales (book_title, copies_sold) VALUES
('The Hobbit', 3),
('1984', 5),
('The Hobbit', 2),
('Dune', 4),
('1984', 1);

SET @sort_order = 'DESC';

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

Need a hint?

Add ORDER BY total_sold DESC to sort books by total sales from highest to lowest.