Why WHERE Doesn’t Work with COUNT() — Understanding HAVING with Real Examples

[Rajeev Bagra]

 

✅ Introduction

When learning SQL, many beginners try to filter grouped data using the WHERE clause and get confused when it doesn’t work.

For example, while working with school data, you may want to find cities that have 3 or fewer public schools.

You might write a query like this:

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

But when you run it, SQL throws an error.

Why?

Because WHERE and COUNT() do not work together the way many people expect.

In this tutorial, we’ll understand:

• Why this query fails
• What HAVING is
• When to use WHERE vs HAVING
• How to write the correct solution

---

📌 Initial Context

Suppose you are analyzing education data and want to answer this question:

Which cities in Massachusetts have 3 or fewer public schools?

To solve this, you need to:

1. Select only public schools
2. Group them by city
3. Count schools per city
4. Keep only cities with 3 or fewer schools
5. Sort the results

This requires aggregation — and that’s where HAVING comes in.

---

❌ The Common Mistake

Many learners try this first:

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

This looks logical, but it is wrong.

Let’s see why.

---

⚠️ Why This Query Fails

1️⃣ cc Does Not Exist in WHERE

The alias cc is created here:

COUNT(*) AS cc

But WHERE runs before SELECT.

So when SQL reads:

AND cc <= 3

It says:

“I don’t know what cc is yet.”

Because cc hasn’t been calculated.

---

2️⃣ WHERE Cannot Filter Aggregates

COUNT(*) is an aggregate function.

WHERE works on individual rows, not on grouped results.

So you cannot filter counts using WHERE.

---

🧠 How SQL Executes This Query

SQL follows this logical order:

1️⃣ FROM
2️⃣ WHERE
3️⃣ GROUP BY
4️⃣ COUNT()
5️⃣ HAVING
6️⃣ SELECT
7️⃣ ORDER BY

Important point:

👉 HAVING runs after COUNT()
👉 WHERE runs before COUNT()

That’s why HAVING is needed.

---

✅ The Correct Solution: Using HAVING

To filter grouped results, use HAVING:

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

Now the query works correctly.

---

🔍 Why This Works

Step by step:

1️⃣ FROM schools → load data
2️⃣ WHERE type = 'Public School' → keep only public schools
3️⃣ GROUP BY city → group by city
4️⃣ COUNT(*) → count schools in each city
5️⃣ HAVING cc <= 3 → keep small groups
6️⃣ SELECT → display results
7️⃣ ORDER BY → sort output

So HAVING filters after counting.

---

📌 Alternative (Safer Version)

Some SQL engines prefer this version:

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

This avoids using the alias in HAVING.

Both versions are correct.

---

🆚 WHERE vs HAVING (Comparison Table)

Feature	WHERE	HAVING
Filters	Rows	Groups
Runs	Before GROUP BY	After GROUP BY
Works with COUNT	❌ No	✅ Yes
Used for	Normal conditions	Aggregates

---

🧠 Easy Memory Trick

Remember this:

WHERE filters rows.
HAVING filters groups.

---

⭐ Real-World Meaning

In this example:

• WHERE removes non-public schools
• GROUP BY groups by city
• COUNT counts schools
• HAVING removes big cities
• ORDER BY sorts results

So you are saying:

“Show me cities that have only a few public schools.”

---

🏁 Key Takeaways

✔ WHERE cannot use COUNT
✔ Aliases are not available in WHERE
✔ Use HAVING for aggregates
✔ GROUP BY must come before HAVING
✔ This is essential for reporting queries

---

📘 Final Answer (Recommended)

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

---

🚀 One-Line Summary

Use WHERE to filter rows.
Use HAVING to filter counts.

---

 

[Author]

Posts