GROUP BY single column in SQL - Mini Project: Build & Apply

Using GROUP BY on a Single Column in SQL

📖 Scenario: You work at a small bookstore. You have a table that records every book sale with the book's genre and the number of copies sold. You want to find out how many copies were sold for each genre.

🎯 Goal: Create an SQL query that groups the sales by the genre column and sums the copies_sold for each genre.

📋 What You'll Learn

Create a table called book_sales with columns genre (text) and copies_sold (integer).

Insert the exact rows given into book_sales.

Write a query that groups the data by genre and sums copies_sold.

Order the results by genre alphabetically.

💡 Why This Matters

🌍 Real World

Grouping sales data by category helps businesses understand which categories perform best.

💼 Career

Data analysts and database developers often use GROUP BY to summarize data for reports.

1

Create the book_sales table and insert data

Write SQL statements to create a table called book_sales with columns genre (text) and copies_sold (integer). Then insert these exact rows: ('Fiction', 10), ('Non-Fiction', 5), ('Fiction', 7), ('Science', 3), ('Non-Fiction', 8).

SQL

-- Create table book_sales and insert rows
-- Your code here

Need a hint?

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

2

Set up the SELECT statement with columns

Write a SQL SELECT statement that selects the genre column and the sum of copies_sold as total_copies from the book_sales table.

SQL

CREATE TABLE book_sales (
    genre TEXT,
    copies_sold INTEGER
);

INSERT INTO book_sales (genre, copies_sold) VALUES
('Fiction', 10),
('Non-Fiction', 5),
('Fiction', 7),
('Science', 3),
('Non-Fiction', 8);

-- Write SELECT statement with genre and sum of copies_sold
-- Your code here

Need a hint?

Use SUM() to add up copies sold and give it an alias total_copies.

3

Add the GROUP BY clause

Add a GROUP BY genre clause to the existing SELECT statement to group the sales by genre.

SQL

CREATE TABLE book_sales (
    genre TEXT,
    copies_sold INTEGER
);

INSERT INTO book_sales (genre, copies_sold) VALUES
('Fiction', 10),
('Non-Fiction', 5),
('Fiction', 7),
('Science', 3),
('Non-Fiction', 8);

SELECT genre, SUM(copies_sold) AS total_copies
FROM book_sales
-- Add GROUP BY genre below
-- Your code here

Need a hint?

Use GROUP BY genre to group rows by the genre column.

4

Order the results by genre alphabetically

Add an ORDER BY genre ASC clause to the query to sort the results alphabetically by genre.

SQL

CREATE TABLE book_sales (
    genre TEXT,
    copies_sold INTEGER
);

INSERT INTO book_sales (genre, copies_sold) VALUES
('Fiction', 10),
('Non-Fiction', 5),
('Fiction', 7),
('Science', 3),
('Non-Fiction', 8);

SELECT genre, SUM(copies_sold) AS total_copies
FROM book_sales
GROUP BY genre
-- Add ORDER BY genre ASC below
-- Your code here

Need a hint?

Use ORDER BY genre ASC to sort genres alphabetically.