SQL HAVING Clause 🔍¶

Mentor's Note: The HAVING clause is the "Filter for Groups". Many students get confused and try to use WHERE to filter their SUM or AVG results. This doesn't work! WHERE filters rows before grouping; HAVING filters the groups after they are calculated. 💡

🌟 The Scenario: The Cricket Team 🏏¶

Imagine you are selecting teams for a tournament. - WHERE: "I only want to look at players from Surat." (Row level filter). - GROUP BY: "Group players by their team names." - HAVING: "I only want teams that have an Average Score > 50." (Group level filter). ✅

💻 1. The Basic Syntax¶

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

Example: High-Budget Departments¶

-- Scenario: List departments that spend more than ₹5,00,000 on salaries
SELECT dept_name, SUM(salary) AS total_budget
FROM employees
GROUP BY dept_name
HAVING SUM(salary) > 500000;

📊 Comparison: WHERE vs HAVING¶

Feature	WHERE Clause	HAVING Clause
When?	Before grouping ⏱️	After grouping 🏁
Applies to	Individual Rows 👤	Summarized Groups 📦
Aggregates?	NO (Cannot use SUM/AVG)	YES (Designed for them)

💻 2. Using Both Together 🪜¶

Professional queries often use both to be super precise.

-- Scenario: Find departments in 'Surat' with more than 5 employees
SELECT dept_name, COUNT(*) AS emp_count
FROM employees
WHERE city = 'Surat'      -- Step 1: Filter rows first
GROUP BY dept_name        -- Step 2: Group them
HAVING COUNT(*) > 5;      -- Step 3: Filter the final groups

🎨 Visual Logic: The Executive Funnel¶

graph TD
    A[Raw Data: 10,000 Rows] --> B[WHERE: Filters to 5,000 Rows]
    B --> C[GROUP BY: Categorizes into 10 Groups]
    C --> D[HAVING: Keeps only 3 High-Value Groups]
    D --> E[Final Result Set 📊]

💡 Interview Tip 👔¶

"If an interviewer asks: 'Can I use HAVING without GROUP BY?' The answer is technically YES. If you use it on a table without a GROUP BY, it treats the whole table as one single group! But in practice, we almost always use them together."