# HAVING Clause

# 📘 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`

| Feature | `WHERE` | `HAVING` |
| --- | --- | --- |
| Filters on | **Rows (individual records)** | **Groups (after aggregation)** |
| Used with | Any SQL query | Only with `GROUP BY` |
| When applied | Before grouping | After grouping |
| Can use aggregates? | ❌ No (`SUM()`, `AVG()`, etc. not allowed) | ✅ Yes (`SUM()`, `COUNT()`, etc.) |

---

## 🧱 Step 1: Create a Sample Table

Let’s work with a simple `Sales` table.

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

---

## 🛠️ Step 2: Insert Data into the Table

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

### 📊 Table: Sales

| SaleID | Product | Quantity |
| --- | --- | --- |
| 1 | Pen | 10 |
| 2 | Pencil | 20 |
| 3 | Eraser | 15 |
| 4 | Pen | 8 |
| 5 | Pencil | 25 |

---

## 🎯 Step 3: Use of `GROUP BY` Only

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

### ✅ Output:

| Product | Total\_Quantity |
| --- | --- |
| Pen | 18 |
| Pencil | 45 |
| Eraser | 15 |

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**

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

### 🔽 Output:

| Product | Total\_Quantity |
| --- | --- |
| Pencil | 45 |

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`.

```sql
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:

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

### ✅ Output:

| Product | Sale\_Count |
| --- | --- |
| Pen | 2 |
| Pencil | 2 |

`Eraser` only appears once, so it is excluded.

---

## 🧠 Quick Recap:

| Clause | Use For | Can use Aggregate? | Comes After GROUP BY? |
| --- | --- | --- | --- |
| `WHERE` | Filter rows | ❌ No | ❌ No |
| `HAVING` | Filter groups | ✅ Yes | ✅ Yes |

---

## 📌 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`