How to Use GROUP BY, COUNT(), and HAVING (Fixing a Common SQL Mistake)

[Rajeev Bagra]

🎯 Problem Context

You are working with a schools table and want to answer:

“How many schools are there in each city, and which cities have 3 or fewer schools?”

This is a classic SQL aggregation problem using:

• GROUP BY
• COUNT()
• HAVING

---

❌ Initial Attempt (Your Query)

SELECT schools.city, COUNT(schools.city) 
FROM schools
GROUP BY schools.city
HAVING COUNT(schools.city <=3) 
ORDER BY COUNT(schools.city), schools.city;

---

🚨 What’s Wrong Here?

1. ❌ Incorrect Use of COUNT()

COUNT(schools.city <= 3)

👉 Problem:

• schools.city <= 3 is a condition (TRUE/FALSE)
• COUNT() expects a column or *, not a boolean expression

---

2. ❌ Logical Misplacement

You want:

“Filter cities where the count is ≤ 3”

But instead of:

COUNT(condition)

You should:

COUNT(column) <= 3

---

✅ Correct Query

SELECT schools.city, COUNT(schools.city) 
FROM schools
GROUP BY schools.city
HAVING COUNT(schools.city) <= 3
ORDER BY COUNT(schools.city), schools.city;

---

💡 Even Better Version (Recommended)

SELECT schools.city, COUNT(*) 
FROM schools
GROUP BY schools.city
HAVING COUNT(*) <= 3
ORDER BY COUNT(*), schools.city;

---

🧠 Step-by-Step Explanation

🔹 Step 1: Group Data

GROUP BY schools.city

👉 Combines all rows with the same city into groups

---

🔹 Step 2: Count Rows per Group

COUNT(*)

👉 Counts how many schools are in each city

---

🔹 Step 3: Filter Groups

HAVING COUNT(*) <= 3

👉 Keeps only cities with 3 or fewer schools

---

🔹 Step 4: Sort Results

ORDER BY COUNT(*), schools.city;

👉 Sorts by:

1. Number of schools (ascending)
2. City name (alphabetically)

---

⚠️ Key Concept: WHERE vs HAVING

Clause	Used For	Example
WHERE	Filtering rows	WHERE city = 'Kolkata'
HAVING	Filtering groups	HAVING COUNT(*) <= 3

---

🧪 Debugging Strategy (Very Useful)

Start simple:

SELECT schools.city, COUNT(*) 
FROM schools
GROUP BY schools.city;

👉 See all counts first
👉 Then add HAVING to filter

---

🚀 Practice Exercise

Try modifying this query:

👉 Find cities with:

• More than 5 schools
• Sorted by highest count first

---

🧩 Final Takeaway

• Use COUNT(*) for counting rows
• Use GROUP BY to aggregate
• Use HAVING to filter aggregated results
• Never put conditions inside COUNT

[Author]

Posts