Understanding WHERE vs HAVING (with GROUP BY)

[Rajeev Bagra]

When working with SQL, one of the most common mistakes learners make is misusing HAVING instead of WHERE—especially when combined with GROUP BY.

Let’s break this down step-by-step using your query.

---

🧩 The Problem Query

SELECT city, COUNT(type)
FROM schools
GROUP BY city
HAVING type = "Public School" AND COUNT(type) <= 3
ORDER BY COUNT(type) DESC, city ASC;

---

❌ Why This Query Is Wrong

1. After GROUP BY, rows become groups

When you write:

GROUP BY city

👉 SQL transforms your table into something like this:

city	types inside group
Pune	Public, Private, Public
Delhi	Public, Public
Mumbai	Private, Private

Now each row represents a group, not individual rows.

---

2. Problem with HAVING type = "Public School"

Inside a group:

• There are multiple values of type
• SQL doesn’t know which one to compare

👉 This becomes:

“Which type should I check for this city?”

❌ This is ambiguous → leads to error or wrong result

---

🧠 Key Concept: WHERE vs HAVING

Clause	Works On	When It Runs
WHERE	Individual rows	Before grouping
HAVING	Groups	After grouping

---

🔍 Correct Way to Think

We want:

“Cities that have ≤ 3 Public Schools”

So we must:

1. First filter only Public Schools
2. Then count them per city
3. Then filter cities based on count

---

✅ Correct Query

SELECT city, COUNT(type)
FROM schools
WHERE type = "Public School"
GROUP BY city
HAVING COUNT(type) <= 3
ORDER BY COUNT(type) DESC, city ASC;

---

🔄 Step-by-Step Transformation

Step 1: Original Table

city	type
Pune	Public School
Pune	Private School
Pune	Public School
Delhi	Public School
Delhi	Public School
Mumbai	Private School

---

Step 2: WHERE filters rows

WHERE type = "Public School"

city	type
Pune	Public School
Pune	Public School
Delhi	Public School
Delhi	Public School

---

Step 3: GROUP BY city

city	count(type)
Pune	2
Delhi	2

---

Step 4: HAVING

HAVING COUNT(type) <= 3

👉 Both cities remain (since 2 ≤ 3)

---

Step 5: ORDER BY

ORDER BY COUNT(type) DESC, city ASC;

👉 Sorted output

---

🔥 Advanced Alternative (Using HAVING Only)

You can write:

SELECT city, COUNT(type)
FROM schools
GROUP BY city
HAVING SUM(type = "Public School") <= 3;

How this works:

• type = "Public School" → becomes 1 or 0
• SUM(...) counts only Public Schools

⚠️ But this is less readable than using WHERE

---

🚨 Common Mistakes to Avoid

❌ Mistake 1

HAVING type = "Public School"

✔ Fix → Use WHERE

---

❌ Mistake 2

Using non-aggregated columns in HAVING

✔ Fix → Only use:

• Aggregates (COUNT, SUM, etc.)
• Or grouped columns

---

💡 Final Rule (Must Remember)

🔹 WHERE filters rows
🔹 HAVING filters groups

---

🎯 Mental Shortcut

Ask yourself:

👉 “Am I filtering individual rows or grouped results?”

• Rows → WHERE
• Groups → HAVING

---

 

[Author]

Posts