GROUP BY with ORDER BY in SQL - Mini Project: Build & Apply

GROUP BY with ORDER BY in SQL

📖 Scenario: You work for a small bookstore. The store keeps a record of every sale in a table called sales. Each sale has the book's title, the author, and the quantity sold.The manager wants to see how many copies of each book have been sold in total, sorted from the most sold to the least sold.

🎯 Goal: Create an SQL query that groups the sales by title and author, sums the total quantity sold for each book, and orders the results by the total quantity sold in descending order.

📋 What You'll Learn

Use the sales table with columns title, author, and quantity

Group the results by title and author

Calculate the total quantity sold for each book using SUM(quantity)

Order the results by the total quantity sold in descending order

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use GROUP BY with ORDER BY to summarize and rank sales data.

💼 Career

Understanding how to group and order data is essential for data analysis and reporting roles.

1

Create the sales table with sample data

Write an SQL statement to create a table called sales with columns title (text), author (text), and quantity (integer). Then insert these exact rows: ('The Hobbit', 'J.R.R. Tolkien', 5), ('The Hobbit', 'J.R.R. Tolkien', 3), ('1984', 'George Orwell', 4), ('1984', 'George Orwell', 2), ('Dune', 'Frank Herbert', 6).

SQL

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

Need a hint?

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

2

Add a variable for the total quantity alias

Write an SQL query that selects title, author, and the sum of quantity as total_quantity from the sales table. Use SUM(quantity) AS total_quantity in the SELECT clause.

SQL

CREATE TABLE sales (
  title TEXT,
  author TEXT,
  quantity INTEGER
);

INSERT INTO sales (title, author, quantity) VALUES
('The Hobbit', 'J.R.R. Tolkien', 5),
('The Hobbit', 'J.R.R. Tolkien', 3),
('1984', 'George Orwell', 4),
('1984', 'George Orwell', 2),
('Dune', 'Frank Herbert', 6);

-- Select title, author, and sum of quantity as total_quantity
SELECT title, author, SUM(quantity) AS total_quantity
FROM sales
-- Your code here

Need a hint?

Use SUM(quantity) AS total_quantity to calculate the total copies sold per book.

3

Group the sales by title and author

Add a GROUP BY clause to the query to group the results by title and author.

SQL

CREATE TABLE sales (
  title TEXT,
  author TEXT,
  quantity INTEGER
);

INSERT INTO sales (title, author, quantity) VALUES
('The Hobbit', 'J.R.R. Tolkien', 5),
('The Hobbit', 'J.R.R. Tolkien', 3),
('1984', 'George Orwell', 4),
('1984', 'George Orwell', 2),
('Dune', 'Frank Herbert', 6);

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

Need a hint?

Use GROUP BY title, author to group the sales by book.

4

Order the results by total quantity sold descending

Add an ORDER BY clause to the query to sort the results by total_quantity in descending order.

SQL

CREATE TABLE sales (
  title TEXT,
  author TEXT,
  quantity INTEGER
);

INSERT INTO sales (title, author, quantity) VALUES
('The Hobbit', 'J.R.R. Tolkien', 5),
('The Hobbit', 'J.R.R. Tolkien', 3),
('1984', 'George Orwell', 4),
('1984', 'George Orwell', 2),
('Dune', 'Frank Herbert', 6);

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

Need a hint?

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