Mastering WHERE, GROUP BY, and HAVING (Fixing a Real SQL Query)

[Rajeev Bagra]

Problem Context

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

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

Additionally, you want the result:

• Sorted by count (descending)
• Then by city name (ascending)

---

❌ Initial Query (Common Mistake)

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

---

🚨 What’s Wrong?

1. ❌ Incorrect Clause Order

SQL follows a strict execution order:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

👉 But the query used:

HAVING ...
GROUP BY ...

❌ This breaks SQL syntax

---

2. ❌ Using HAVING Instead of WHERE

HAVING type = "Public School"

👉 Problem:

• HAVING filters groups
• This condition filters rows

✅ Must use WHERE

---

3. ❌ Using Alias in HAVING

cc <= 3

👉 Not always supported across SQL engines
👉 Safer to use:

COUNT(*) <= 3

---

✅ Correct Query

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

---

🧠 Step-by-Step Breakdown

---

🔹 Step 1: Filter Rows (WHERE)

WHERE type = "Public School"

👉 Keeps only rows where school type is “Public School”

---

🔹 Step 2: Group Rows (GROUP BY)

GROUP BY city

👉 Groups all remaining rows by city

---

🔹 Step 3: Aggregate (COUNT)

COUNT(*)

👉 Counts how many public schools exist per city

---

🔹 Step 4: Filter Groups (HAVING)

HAVING COUNT(*) <= 3

👉 Keeps only cities with ≤ 3 public schools

---

🔹 Step 5: Sort Results (ORDER BY)

ORDER BY cc DESC, city ASC;

👉 Sorts:

1. Highest count first
2. Then city alphabetically

---

⚖️ WHERE vs HAVING (Critical Concept)

Clause	Works On	Example
WHERE	Individual rows	type = "Public School"
HAVING	Groups	COUNT(*) <= 3

---

🧪 Debugging Strategy (Very Important)

Step 1: Check filtering

SELECT * FROM schools
WHERE type = "Public School";

Step 2: Check grouping

SELECT city, COUNT(*) 
FROM schools
WHERE type = "Public School"
GROUP BY city;

Step 3: Add filtering on groups

HAVING COUNT(*) <= 3;

---

💡 Pro Tips

• Use COUNT(*) instead of COUNT(column) unless needed
• Always remember SQL order
• Think in phases:
  1. Filter rows
  2. Group data
  3. Aggregate
  4. Filter groups
  5. Sort

---

🚀 Practice Exercise

Modify the query to:

👉 Find cities with:

• More than 5 public schools
• Sorted by city name only

---

🧩 Final Takeaways

• WHERE filters before grouping
• GROUP BY creates groups
• HAVING filters after grouping
• ORDER BY controls final output order

---

 

[Author]

Posts