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

Filtering Groups with HAVING in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to find authors who have sold more than a certain number of books in total.

🎯 Goal: Build a query that groups sales by author and uses HAVING to filter authors with total sales above a threshold.

📋 What You'll Learn

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

Insert the exact sales data provided.

Create a variable min_sales to set the minimum total copies sold to filter authors.

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

Use HAVING to filter groups where total copies sold is greater than min_sales.

💡 Why This Matters

🌍 Real World

Filtering grouped data is common in sales reports, analytics, and business intelligence to find top performers or categories.

💼 Career

Understanding how to use HAVING helps database analysts and developers write efficient queries that summarize and filter grouped data.

1

Create the book_sales table and insert data

Create a table called book_sales with columns author (text) and copies_sold (integer). Then insert these exact rows: ('Alice Munro', 120), ('Alice Munro', 80), ('Gabriel Garcia Marquez', 200), ('Gabriel Garcia Marquez', 150), ('Haruki Murakami', 90).

PostgreSQL

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

Need a hint?

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

2

Set the minimum sales threshold

Create a variable called min_sales and set it to 200 to represent the minimum total copies sold to filter authors.

PostgreSQL

CREATE TABLE book_sales (
    author TEXT,
    copies_sold INTEGER
);

INSERT INTO book_sales (author, copies_sold) VALUES
('Alice Munro', 120),
('Alice Munro', 80),
('Gabriel Garcia Marquez', 200),
('Gabriel Garcia Marquez', 150),
('Haruki Murakami', 90);

-- Create variable min_sales and set to 200
-- Your code here

Need a hint?

Use \set min_sales 200 to create a psql variable for filtering.

3

Write a query to group sales by author and sum copies

Write a query that selects author and the sum of copies_sold as total_sold. Group the results by author.

PostgreSQL

CREATE TABLE book_sales (
    author TEXT,
    copies_sold INTEGER
);

INSERT INTO book_sales (author, copies_sold) VALUES
('Alice Munro', 120),
('Alice Munro', 80),
('Gabriel Garcia Marquez', 200),
('Gabriel Garcia Marquez', 150),
('Haruki Murakami', 90);

\set min_sales 200

-- Write query to select author and sum(copies_sold) as total_sold grouped by author
-- Your code here

Need a hint?

Use GROUP BY author and SUM(copies_sold) to get total sales per author.

4

Add HAVING clause to filter authors by total sales

Add a HAVING clause to the query to only include authors where SUM(copies_sold) is greater than the variable min_sales.

PostgreSQL

CREATE TABLE book_sales (
    author TEXT,
    copies_sold INTEGER
);

INSERT INTO book_sales (author, copies_sold) VALUES
('Alice Munro', 120),
('Alice Munro', 80),
('Gabriel Garcia Marquez', 200),
('Gabriel Garcia Marquez', 150),
('Haruki Murakami', 90);

\set min_sales 200

SELECT author, SUM(copies_sold) AS total_sold
FROM book_sales
GROUP BY author
-- Add HAVING clause to filter authors with total_sold > min_sales
-- Your code here

Need a hint?

Use HAVING SUM(copies_sold) > :min_sales to filter groups after grouping.