HAVING clause in MySQL - Mini Project: Build & Apply

Filter Groups Using HAVING Clause in MySQL

📖 Scenario: You are managing a small store's sales database. You want to find which products have sold more than a certain number of units in total. This helps you understand your best-selling products.

🎯 Goal: Build a MySQL query that groups sales by product and uses the HAVING clause to show only products with total sales greater than a set threshold.

📋 What You'll Learn

Create a table called sales with columns product (text) and quantity (integer).

Insert the exact sales data provided.

Set a variable min_sales to 10 to use as the threshold.

Write a query that groups sales by product and sums quantity.

Use the HAVING clause to filter groups where total quantity is greater than min_sales.

💡 Why This Matters

🌍 Real World

Filtering grouped data is common in sales reports, inventory management, and any situation where you want to analyze summarized data.

💼 Career

Knowing how to use GROUP BY with HAVING is essential for data analysts, database administrators, and backend developers working with SQL databases.

1

Create the sales table and insert data

Create a table called sales with columns product (VARCHAR) and quantity (INT). Then insert these exact rows: ('Apples', 5), ('Bananas', 8), ('Apples', 7), ('Oranges', 3), ('Bananas', 4), ('Oranges', 10).

MySQL

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

Need a hint?

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

2

Set the minimum sales threshold

Create a variable called min_sales and set it to 10. This will be the threshold for filtering products by total sales.

MySQL

CREATE TABLE sales (
  product VARCHAR(50),
  quantity INT
);

INSERT INTO sales (product, quantity) VALUES
('Apples', 5),
('Bananas', 8),
('Apples', 7),
('Oranges', 3),
('Bananas', 4),
('Oranges', 10);

-- Set min_sales to 10
-- Your code here

Need a hint?

Use SET @min_sales = 10; to create a user variable in MySQL.

3

Write the query to group sales by product and sum quantities

Write a SELECT query that groups rows by product and calculates the total quantity sold for each product using SUM(quantity). Use GROUP BY product.

MySQL

CREATE TABLE sales (
  product VARCHAR(50),
  quantity INT
);

INSERT INTO sales (product, quantity) VALUES
('Apples', 5),
('Bananas', 8),
('Apples', 7),
('Oranges', 3),
('Bananas', 4),
('Oranges', 10);

SET @min_sales = 10;

-- Write SELECT query to group by product and sum quantity
-- Your code here

Need a hint?

Use SELECT product, SUM(quantity) AS total_quantity FROM sales GROUP BY product;

4

Add HAVING clause to filter products with total sales greater than min_sales

Add a HAVING clause to the query to show only products where SUM(quantity) is greater than the variable @min_sales.

MySQL

CREATE TABLE sales (
  product VARCHAR(50),
  quantity INT
);

INSERT INTO sales (product, quantity) VALUES
('Apples', 5),
('Bananas', 8),
('Apples', 7),
('Oranges', 3),
('Bananas', 4),
('Oranges', 10);

SET @min_sales = 10;

SELECT product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product
-- Add HAVING clause to filter total_quantity > @min_sales
-- Your code here

Need a hint?

Use HAVING SUM(quantity) > @min_sales after the GROUP BY clause.