📘 Mastering SQL HAVING Clause – From Scratch

Working with aggregate functions in SQL often requires filtering grouped data. This is where the HAVING clause becomes essential. Many beginners confuse it with the WHERE clause — but they serve different purposes.

In this blog, we’ll break everything down step-by-step with a real table, insertion commands, and examples. Let’s begin!

---

📌 What is the HAVING Clause?

The HAVING clause in SQL is used to filter groups of records created by the GROUP BY clause based on a condition applied to aggregate functions like SUM(), COUNT(), AVG(), etc.

---

🔍 Difference Between WHERE and HAVING

---

🧱 Step 1: Create a Sample Table

Let’s work with a simple Sales table.

CREATE TABLE Sales (
    SaleID INT,
    Product VARCHAR(50),
    Quantity INT
);

---

🛠️ Step 2: Insert Data into the Table

INSERT INTO Sales (SaleID, Product, Quantity) VALUES
(1, 'Pen', 10),
(2, 'Pencil', 20),
(3, 'Eraser', 15),
(4, 'Pen', 8),
(5, 'Pencil', 25);

📊 Table: Sales

---

🎯 Step 3: Use of GROUP BY Only

SELECT Product, SUM(Quantity) AS Total_Quantity
FROM Sales
GROUP BY Product;

✅ Output:

This groups products and sums the quantities, but shows all products.

---

✅ Step 4: Filtering Groups Using HAVING

✨ Requirement: Show only products with total quantity greater than 20

SELECT Product, SUM(Quantity) AS Total_Quantity
FROM Sales
GROUP BY Product
HAVING SUM(Quantity) > 20;

🔽 Output:

Only Pencil has a total quantity above 20 — filtered by the HAVING clause.

---

🧠 Step 5: Compare with WHERE Clause

Let’s say we want to ignore sales with quantity ≤ 5 before grouping. We use WHERE.

SELECT Product, SUM(Quantity) AS Total_Quantity
FROM Sales
WHERE Quantity > 5
GROUP BY Product
HAVING SUM(Quantity) > 20;

🔄 Flow:

• WHERE Quantity > 5 → Filters rows before grouping

• GROUP BY Product → Groups remaining rows

• HAVING SUM(Quantity) > 20 → Filters final grouped result

---

🧪 Extra Example: Using COUNT() with HAVING

Let’s count how many times each product was sold, and show only those with more than one sale:

SELECT Product, COUNT(*) AS Sale_Count
FROM Sales
GROUP BY Product
HAVING COUNT(*) > 1;

✅ Output:

Eraser only appears once, so it is excluded.

---

🧠 Quick Recap:

---

📌 Final Thoughts

• Use WHERE when filtering before grouping (like filtering rows).

• Use HAVING when filtering after aggregation (like filtering total sums or counts).

• You can combine both in the same query.

---

🙋 Want to Practice?

Try writing a query that:

• Shows only products whose average quantity per sale is greater than 10

• Hint: Use AVG(Quantity) in HAVING