HAVING clause for filtering groups in SQL - Mini Project: Build & Apply

Filtering Grouped Data Using HAVING Clause

📖 Scenario: You are managing a small bookstore's sales database. You want to find which books have sold more than a certain number of copies to decide which books to promote.

🎯 Goal: Build an SQL query that groups sales by book title and filters groups using the HAVING clause to show only books with total sales above a threshold.

📋 What You'll Learn

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

Insert the exact sales data provided into the sales table.

Write a query that groups sales by book_title and sums copies_sold.

Add a HAVING clause to filter groups where total copies sold is greater than 100.

💡 Why This Matters

🌍 Real World

Filtering grouped data is common in sales reports, inventory management, and any scenario where you want to analyze summarized data and apply conditions on those summaries.

💼 Career

Knowing how to use GROUP BY with HAVING is essential for data analysts, database administrators, and backend developers who work with databases to generate meaningful reports.

1

Create the sales table and insert data

Create a table called sales with columns book_title as TEXT and copies_sold as INTEGER. Then insert these exact rows: ('The Alchemist', 50), ('The Alchemist', 60), ('1984', 80), ('1984', 30), ('Brave New World', 90), ('Brave New World', 20).

SQL

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

Need a hint?

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

2

Set the sales threshold variable

Create a variable or placeholder called sales_threshold and set it to 100 to use as the minimum total copies sold for filtering.

SQL

CREATE TABLE sales (book_title TEXT, copies_sold INTEGER);
INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 50),
('The Alchemist', 60),
('1984', 80),
('1984', 30),
('Brave New World', 90),
('Brave New World', 20);
-- Set sales_threshold to 100
-- Your code here

Need a hint?

SQL does not always support variables in all environments; you can just note the threshold value 100 for use in the next step.

3

Write the query to group sales by book and sum copies

Write an SQL query that selects book_title and the sum of copies_sold as total_copies from the sales table, grouping by book_title.

SQL

CREATE TABLE sales (book_title TEXT, copies_sold INTEGER);
INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 50),
('The Alchemist', 60),
('1984', 80),
('1984', 30),
('Brave New World', 90),
('Brave New World', 20);
-- Set sales_threshold to 100
-- This is a placeholder for the threshold value
-- In SQL scripts, you can use a variable or just remember this value
-- For this project, just note the threshold as 100

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

Need a hint?

Use GROUP BY book_title and SUM(copies_sold) to get total copies sold per book.

4

Add the HAVING clause to filter groups with total copies sold > 100

Add a HAVING clause to the previous query to show only books where SUM(copies_sold) is greater than 100.

SQL

CREATE TABLE sales (book_title TEXT, copies_sold INTEGER);
INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 50),
('The Alchemist', 60),
('1984', 80),
('1984', 30),
('Brave New World', 90),
('Brave New World', 20);
-- Set sales_threshold to 100
-- This is a placeholder for the threshold value
-- In SQL scripts, you can use a variable or just remember this value
-- For this project, just note the threshold as 100

SELECT book_title, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY book_title
-- Add HAVING clause to filter total_copies > 100
-- Your code here

Need a hint?

Use HAVING SUM(copies_sold) > 100 after the GROUP BY clause to filter groups.