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

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

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

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

Hint

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

Practice

1.

What is the main purpose of the HAVING clause in SQL?

easy

A. To filter individual rows before grouping

B. To sort the results of a query

C. To filter groups created by GROUP BY based on aggregate conditions

D. To join two tables together

Solution

1. Step 1: Understand the role of GROUP BY

   The GROUP BY clause groups rows based on column values.

2. Step 2: Identify the purpose of HAVING

   HAVING filters these groups using aggregate functions like SUM or COUNT.

3. Final Answer:

   To filter groups created by GROUP BY based on aggregate conditions -> Option C

4. Quick Check:

   HAVING filters groups, not rows [OK]

Hint: Remember: WHERE filters rows, HAVING filters groups [OK]

Common Mistakes:

• Confusing HAVING with WHERE clause
• Using HAVING without GROUP BY
• Thinking HAVING sorts data

2.

Which of the following is the correct syntax to filter groups with HAVING?

SELECT department, COUNT(*) FROM employees GROUP BY department _______ COUNT(*) > 5;

easy

A. GROUP BY

B. WHERE

C. FILTER

D. HAVING

Solution

1. Step 1: Identify filtering clause after grouping

   After GROUP BY, filtering groups requires HAVING, not WHERE.

2. Step 2: Confirm correct clause usage

   HAVING COUNT(*) > 5 filters groups with more than 5 employees.

3. Final Answer:

   HAVING -> Option D

4. Quick Check:

   Use HAVING after GROUP BY [OK]

Hint: Use HAVING to filter groups, not WHERE [OK]

Common Mistakes:

• Using WHERE instead of HAVING after GROUP BY
• Placing HAVING before GROUP BY
• Using FILTER keyword which is invalid here

3.

Given the table sales with columns region and amount, what will this query return?

SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 1000;

medium

A. All regions with total sales greater than 1000

B. All regions with total sales less than or equal to 1000

C. All sales records with amount greater than 1000

D. Syntax error due to HAVING clause

Solution

1. Step 1: Group sales by region and sum amounts

   The query groups rows by region and calculates total amount per region.

2. Step 2: Filter groups with total sales > 1000

   The HAVING clause keeps only regions where the sum is greater than 1000.

3. Final Answer:

   All regions with total sales greater than 1000 -> Option A

4. Quick Check:

   HAVING filters groups by aggregate sum [OK]

Hint: HAVING filters groups by aggregate results [OK]

Common Mistakes:

• Thinking HAVING filters individual rows
• Confusing SUM(amount) with amount column
• Assuming syntax error with HAVING

4.

Identify the error in this query:

SELECT category, COUNT(*) FROM products HAVING COUNT(*) > 10 GROUP BY category;

medium

A. Missing WHERE clause before HAVING

B. HAVING clause used before GROUP BY

C. COUNT(*) cannot be used in HAVING

D. GROUP BY should be replaced with ORDER BY

Solution

1. Step 1: Check order of clauses in SQL

   The correct order is GROUP BY first, then HAVING.

2. Step 2: Identify incorrect clause order

   The query places HAVING before GROUP BY, causing syntax error.

3. Final Answer:

   HAVING clause used before GROUP BY -> Option B

4. Quick Check:

   GROUP BY before HAVING [OK]

Hint: GROUP BY must come before HAVING [OK]

Common Mistakes:

• Placing HAVING before GROUP BY
• Thinking HAVING needs WHERE before it
• Replacing GROUP BY with ORDER BY incorrectly

5.

You have a students table with columns class and score. You want to find classes where the average score is at least 75 and the number of students is more than 10. Which query achieves this?

hard

A. SELECT class, AVG(score), COUNT(*) FROM students GROUP BY class HAVING AVG(score) >= 75 AND COUNT(*) > 10;

B. SELECT class, AVG(score), COUNT(*) FROM students HAVING AVG(score) >= 75 AND COUNT(*) > 10 GROUP BY class;

C. SELECT class, AVG(score), COUNT(*) FROM students WHERE AVG(score) >= 75 AND COUNT(*) > 10 GROUP BY class;

D. SELECT class, AVG(score), COUNT(*) FROM students GROUP BY class WHERE AVG(score) >= 75 AND COUNT(*) > 10;

Solution

1. Step 1: Group students by class

   Use GROUP BY class to group rows by class.

2. Step 2: Filter groups with HAVING using aggregate conditions

   Use HAVING AVG(score) >= 75 AND COUNT(*) > 10 to keep classes meeting both conditions.

3. Final Answer:

   SELECT class, AVG(score), COUNT(*) FROM students GROUP BY class HAVING AVG(score) >= 75 AND COUNT(*) > 10; -> Option A

4. Quick Check:

   HAVING filters groups by multiple aggregates [OK]

Hint: Use HAVING for multiple aggregate filters after GROUP BY [OK]

Common Mistakes:

• Placing HAVING before GROUP BY
• Using WHERE with aggregate functions
• Putting WHERE after GROUP BY