📘 Learning Post: Using SQL Subqueries with WHERE and AND (Fixing a Common Mistake)

[Rajeev Bagra]

When working with SQL, it’s very common to compare values against averages using subqueries. But small syntax mistakes can break your query — like using multiple WHERE clauses.

Let’s walk through this step-by-step.

---

🎯 Problem Statement

We want to find:

Districts that spend more than average per student AND have above-average exemplary staff ratings

---

❌ Initial (Incorrect) Query

SELECT districts.name, expenditures.per_pupil_expenditure, staff_evaluations.exemplary
FROM districts
JOIN expenditures
ON districts.id = expenditures.district_id
JOIN staff_evaluations
ON districts.id = staff_evaluations.district_id
WHERE expenditures.per_pupil_expenditure >
(SELECT AVG(expenditures.per_pupil_expenditure)
FROM expenditures) 
AND
WHERE staff_evaluations.exemplary > (
SELECT AVG(staff_evaluations.exemplary) 
FROM staff_evaluations;

---

🚨 What Went Wrong?

1. ❌ Two WHERE Clauses

SQL allows only one WHERE clause per query.

👉 You must combine conditions using:

• AND
• OR

---

2. ❌ Missing Parenthesis

The second subquery is not properly closed:

FROM staff_evaluations;

Needs a closing ) before the semicolon.

---

✅ Correct Query

SELECT districts.name,
       expenditures.per_pupil_expenditure,
       staff_evaluations.exemplary
FROM districts
JOIN expenditures
  ON districts.id = expenditures.district_id
JOIN staff_evaluations
  ON districts.id = staff_evaluations.district_id
WHERE expenditures.per_pupil_expenditure >
      (SELECT AVG(per_pupil_expenditure)
       FROM expenditures)
  AND staff_evaluations.exemplary >
      (SELECT AVG(exemplary)
       FROM staff_evaluations);

---

🧠 Concept Breakdown

🔹 1. Subqueries

A subquery is a query inside another query.

Example:

SELECT AVG(per_pupil_expenditure) FROM expenditures;

👉 This returns a single value (average), which we compare against.

---

🔹 2. Combining Conditions

Instead of:

WHERE condition1
AND
WHERE condition2  ❌

Use:

WHERE condition1
AND condition2    ✅

---

🔹 3. Logical Flow

SQL evaluates like this:

1. Calculate average expenditure
2. Calculate average exemplary score
3. Filter districts:
   • Keep only those above both averages

---

🧪 Debugging Tip (Very Important)

Always test subqueries separately:

SELECT AVG(per_pupil_expenditure) FROM expenditures;
SELECT AVG(exemplary) FROM staff_evaluations;

👉 This helps you:

• Verify correctness
• Understand what your main query is comparing against

---

💡 Pro Insight

This type of query is very common in:

• 📊 Data analysis
• 🏫 Education datasets
• 💼 Business intelligence dashboards

---

🚀 Practice Exercise

Try modifying the query to find:

👉 Districts that:

• Spend below average
• But still have above-average exemplary ratings

---

•  

[Author]

Posts