Why aggregation is needed in SQL - See It in Action

Understanding Why Aggregation is Needed in SQL

📖 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 find out the total copies sold for each book to understand which books are popular.

🎯 Goal: Build a simple SQL query that uses aggregation to calculate the total copies sold for each book.

📋 What You'll Learn

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

Insert the exact sales data provided.

Write a query that uses GROUP BY on book_title.

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

💡 Why This Matters

🌍 Real World

Stores and businesses use aggregation to understand total sales, average ratings, or counts of items sold.

💼 Career

Knowing how to write aggregation queries is essential for data analysts and anyone working with databases to summarize and report data.

1

Create the sales table and insert data

Create a table called sales with columns book_title (text) and copies_sold (integer). Then insert these exact rows: ('The Alchemist', 5), ('The Alchemist', 3), ('1984', 4), ('1984', 6), ('Brave New World', 2).

SQL

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

Need a hint?

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

2

Set up the aggregation query structure

Write a SQL query that selects book_title and prepares to calculate total copies sold by grouping the rows by book_title. Start the query with SELECT book_title and add GROUP BY book_title at the end.

SQL

CREATE TABLE sales (
  book_title TEXT,
  copies_sold INTEGER
);

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

-- Write SELECT and GROUP BY clauses
-- Your code here

Need a hint?

Use GROUP BY book_title to group rows by book title.

3

Add the aggregation function to calculate total copies sold

Modify the query to include SUM(copies_sold) as total_copies in the SELECT clause to calculate the total copies sold for each book.

SQL

CREATE TABLE sales (
  book_title TEXT,
  copies_sold INTEGER
);

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

SELECT book_title, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY book_title;
-- Your code here

Need a hint?

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

4

Complete the query to show why aggregation is needed

Ensure the query selects book_title and the total copies sold as total_copies grouped by book_title. This final query shows the total sales per book, demonstrating why aggregation is needed to summarize data.

SQL

CREATE TABLE sales (
  book_title TEXT,
  copies_sold INTEGER
);

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

SELECT book_title, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY book_title;
-- Finalize the query to show total sales per book

Need a hint?

This query shows total copies sold per book, which is why aggregation is needed.