How GROUP BY changes query execution in SQL - Try It Yourself

How GROUP BY changes query execution

📖 Scenario: You work at a small bookstore that keeps track of sales in a database table called sales. Each sale records the book_title, the genre of the book, and the quantity sold.Your manager wants to understand how many books were sold in total for each genre to decide which genres to promote.

🎯 Goal: Build a SQL query that groups sales by genre and calculates the total quantity sold for each genre using GROUP BY.

📋 What You'll Learn

Create a table called sales with columns book_title (text), genre (text), and quantity (integer).

Insert the exact sales data provided.

Write a query that groups the sales by genre.

Calculate the total quantity sold per genre using SUM(quantity).

Use GROUP BY genre in the query.

💡 Why This Matters

🌍 Real World

Grouping sales data by category helps businesses understand which product types sell best and make informed marketing decisions.

💼 Career

Data analysts and database developers often write GROUP BY queries to summarize and report data efficiently.

1

Create the sales table and insert data

Write SQL statements to create a table called sales with columns book_title (text), genre (text), and quantity (integer). Then insert these exact rows into sales: ('The Hobbit', 'Fantasy', 5), ('1984', 'Dystopian', 8), ('The Silmarillion', 'Fantasy', 3), ('Brave New World', 'Dystopian', 6), ('The Catcher in the Rye', 'Classic', 4).

SQL

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

Need a hint?

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

2

Add a variable for the aggregation column

Define a variable or alias called total_quantity that will hold the sum of quantity for each group. This will be used in the next query step.

SQL

CREATE TABLE sales (
    book_title TEXT,
    genre TEXT,
    quantity INTEGER
);

INSERT INTO sales (book_title, genre, quantity) VALUES
('The Hobbit', 'Fantasy', 5),
('1984', 'Dystopian', 8),
('The Silmarillion', 'Fantasy', 3),
('Brave New World', 'Dystopian', 6),
('The Catcher in the Rye', 'Classic', 4);

-- Define an alias for sum(quantity) as total_quantity
-- Your code here

Need a hint?

In SQL, you create an alias for the sum inside the SELECT statement using SUM(quantity) AS total_quantity. This will be done in the next step.

3

Write the query to group sales by genre and sum quantity

Write a SQL query that selects genre and the sum of quantity as total_quantity from the sales table. Use GROUP BY genre to group the rows by genre.

SQL

CREATE TABLE sales (
    book_title TEXT,
    genre TEXT,
    quantity INTEGER
);

INSERT INTO sales (book_title, genre, quantity) VALUES
('The Hobbit', 'Fantasy', 5),
('1984', 'Dystopian', 8),
('The Silmarillion', 'Fantasy', 3),
('Brave New World', 'Dystopian', 6),
('The Catcher in the Rye', 'Classic', 4);

SELECT genre, SUM(quantity) AS total_quantity
FROM sales
GROUP BY genre;
-- Your code here

Need a hint?

Use SELECT genre, SUM(quantity) AS total_quantity FROM sales GROUP BY genre to group and sum.

4

Complete the query with ordering by total quantity

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

SQL

CREATE TABLE sales (
    book_title TEXT,
    genre TEXT,
    quantity INTEGER
);

INSERT INTO sales (book_title, genre, quantity) VALUES
('The Hobbit', 'Fantasy', 5),
('1984', 'Dystopian', 8),
('The Silmarillion', 'Fantasy', 3),
('Brave New World', 'Dystopian', 6),
('The Catcher in the Rye', 'Classic', 4);

SELECT genre, SUM(quantity) AS total_quantity
FROM sales
GROUP BY genre
ORDER BY total_quantity DESC;
-- Your code here

Need a hint?

Use ORDER BY total_quantity DESC to sort from highest to lowest total quantity.