Having – Guided Study Workspace

Introduction: The Problem HAVING Solves

In SQL, the WHERE clause is your everyday tool for filtering individual records. For instance, you can easily ask: "Give me all employees whose salary is above ₹60,000" or "Give me all orders shipped to Mumbai".

However, real-world analytical questions rarely stop at individual rows. Business questions almost always demand summaries:

HR Director: "Show me only departments that have more than 5 employees."
Finance Lead: "Show me only product categories whose total sales exceed ₹1,00,000."
Operations Head: "Show me only warehouse hubs where the average delivery time is over 4 days."

If you try writing WHERE COUNT(*) > 5, SQL will immediately reject your query with a syntax error. Why? Because WHERE inspects rows beforegroups are formed. At that early stage, the database hasn't calculated the group totals yet. This is exactly why SQL introduced the HAVING clause.

What Does HAVING Do?

The HAVING clause filters the groups produced by GROUP BY, typically using the result of an aggregate function such as COUNT(), SUM(), AVG(), MIN(), or MAX().

SELECT department, COUNT(*) AS employees FROM employees GROUP BY department HAVING COUNT(*) > 5;

As formally documented in PostgreSQL and SQL standard specifications: "The HAVING clause turns a table expression into a grouped table, and rows in the resulting table are eliminated if they do not satisfy the condition in the HAVING clause."

Key Mental Model: Just as WHERE filters individual source rows, HAVING filters aggregate summary rows (the group buckets).

WHERE vs HAVING: The Fundamental Distinction

Understanding the difference between WHERE and HAVING is the single most crucial milestone in SQL querying. Here is how they operate:

Feature	WHERE Clause	HAVING Clause
Operates On	Individual raw rows	Aggregated groups / buckets
Execution Timing	BEFORE grouping (before GROUP BY)	AFTER grouping (after GROUP BY)
Aggregate Functions?	❌ No (e.g. `WHERE SUM(x) > 10` is invalid)	✅ Yes (e.g. `HAVING SUM(x) > 10` is standard)
Purpose	Filters which rows participate in grouping	Filters which summary groups appear in final output

Diagram 1 — WHERE vs HAVING Query Processing Pipeline

📦 All Source Rows in Database

↓ (WHERE evaluates row-by-row)

🔍 Filtered Individual Rows (non-matching rows discarded)

↓ (GROUP BY bundles matching rows into buckets)

🗂️ Group Buckets Formed & Aggregate Functions Computed

↓ (HAVING evaluates each group bucket's aggregate result)

✨ Remaining Summary Groups Sent to Output

Let's examine a combined query step-by-step:

SELECT department, COUNT(*) AS mumbai_staff FROM employees WHERE city = 'Mumbai' GROUP BY department HAVING COUNT(*) >= 3;

Step 1 (WHERE): SQL looks at all rows and discards anyone not located in 'Mumbai'.
Step 2 (GROUP BY): The remaining Mumbai employees are grouped by department.
Step 3 (COUNT): SQL counts the Mumbai employees in each department group.
Step 4 (HAVING): SQL filters the groups, keeping only departments that have 3 or more Mumbai employees.

HAVING With COUNT

COUNT() is the most common aggregate used with HAVING. It allows you to filter groups based on group size or transaction count.

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5;

Diagram 2 — COUNT + HAVING Group Evaluation

Sales Group ➔ 8 employees ➔ HAVING COUNT(*) > 5 ➔ ✓ KEEP (8 > 5)

HR Group ➔ 3 employees ➔ HAVING COUNT(*) > 5 ➔ ✗ REMOVE (3 is not > 5)

IT Group ➔ 7 employees ➔ HAVING COUNT(*) > 5 ➔ ✓ KEEP (7 > 5)

HAVING With SUM

When analyzing financial transactions, sales, or quantities, you often need to find categories or customers whose cumulative total exceeds a target threshold:

SELECT category, SUM(amount) AS total_sales FROM sales GROUP BY category HAVING SUM(amount) > 100000;

Here, individual rows with small amounts (e.g. ₹500 items) are not removed individually. Instead, all items in a category are summed up, and if the category total exceeds ₹1,00,000, the category is retained in the result.

HAVING With AVG

You can also filter groups based on their arithmetic mean using AVG():

SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department HAVING AVG(salary) > 50000;

Notice that an employee earning ₹30,000 in the Engineering department will still participate in the calculation. If the overall Engineering department average is ₹75,000, the Engineering group satisfies the condition and is shown.

HAVING With MIN / MAX

MIN() and MAX() can be used in HAVING when you want to filter groups based on extremes, such as finding categories that contain at least one high-ticket item:

SELECT category, MAX(price) AS highest_price FROM products GROUP BY category HAVING MAX(price) > 1000;

This retains any product category whose most expensive item costs more than ₹1,000.

HAVING Without GROUP BY (Advanced Note)

In SQL standards (including PostgreSQL, MySQL, SQL Server, SQLite), a query can technically have a HAVING clause without an explicit GROUP BY clause.

SELECT AVG(salary) FROM employees HAVING AVG(salary) > 50000;

In this case, the entire table is treated as a single group. If the overall table average exceeds ₹50,000, one row is returned. If not, zero rows are returned. While syntactically valid, in everyday practice HAVING is almost always paired with GROUP BY.

Multiple HAVING Conditions

Just like WHERE, you can combine multiple conditions in HAVING using boolean logical operators like AND, OR, and NOT:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_pay FROM employees GROUP BY department HAVING COUNT(*) > 5 AND AVG(salary) > 50000;

A department will only appear in the final output if it satisfies both requirements: more than 5 members AND an average compensation over ₹50,000.

Understanding the Full Query Flow

Let's see how all the clauses work together in a complete SQL query:

SELECT department, COUNT(*) AS employees, AVG(salary) AS average_salary FROM employees WHERE city = 'Mumbai' GROUP BY department HAVING COUNT(*) >= 3 ORDER BY employees DESC;

Diagram 3 — Conceptual Execution Order

1️⃣ FROM employees ➔ Load base table rows

↓

2️⃣ WHERE city = 'Mumbai'➔ Filter & remove non-Mumbai rows

↓

3️⃣ GROUP BY department ➔ Bundle remaining rows into department buckets

↓

4️⃣ Calculate Aggregates ➔ Compute COUNT(*) and AVG(salary) per group

↓

5️⃣ HAVING COUNT(*) >= 3➔ Filter & remove entire groups with < 3 employees

↓

6️⃣ SELECT department, COUNT(*), AVG(salary) ➔ Format chosen output columns

↓

7️⃣ ORDER BY employees DESC ➔ Sort final output rows

Common HAVING Mistakes

Common Mistake	Incorrect SQL ❌	Correct SQL ✅
Filtering aggregates in WHERE	`WHERE COUNT() > 5` (Syntax Error: aggregate functions not allowed in WHERE)*	`HAVING COUNT(*) > 5`
Filtering row columns in HAVING	`HAVING city = 'Mumbai'` (Inefficient & invalid in strict SQL if city not in GROUP BY)	`WHERE city = 'Mumbai'` (Filter raw rows early with WHERE)
Forgetting GROUP BY	`SELECT dept, COUNT() FROM emp HAVING COUNT() > 5;`	`SELECT dept, COUNT() FROM emp GROUP BY dept HAVING COUNT() > 5;`
Confusing row condition with group condition	Want departments with any salary > 50k but writing `HAVING salary > 50k`	`HAVING MAX(salary) > 50000` or `WHERE salary > 50000` depending on intent

Practical HAVING Exercises

Test your understanding by writing mental SQL queries for these progressive scenarios:

Task 1: Large Departments

Find all departments with more than 5 employees.

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

Task 2: High-Volume Sales Categories

Find product categories with cumulative sales exceeding ₹1,00,000.

SELECT category, SUM(amount) FROM sales GROUP BY category HAVING SUM(amount) > 100000;

Task 3: Well-Compensated Teams

Find departments with an average salary greater than ₹50,000.

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

Task 4: Premium Inventory Categories

Find categories whose maximum product price is greater than ₹1,000.

SELECT category, MAX(price) FROM products GROUP BY category HAVING MAX(price) > 1000;

Task 5: Multi-Stage Filtering (WHERE + GROUP BY + HAVING)

Find departments in 'Delhi' that have at least 2 employees.

SELECT department, COUNT(*) FROM employees WHERE city = 'Delhi' GROUP BY department HAVING COUNT(*) >= 2;

HAVING Best Practices

✓Use WHERE for row filters: Filter out unwanted rows as early as possible before grouping.

✓Use HAVING strictly for aggregates: Reserve HAVING for COUNT(), SUM(), AVG(), etc.

✓Always pair with GROUP BY: Keep queries readable and intention clear by explicitly stating GROUP BY columns.

✓Watch for empty results: If all groups fail the condition, HAVING returns 0 rows (which is normal).

What You Should Know Now

✓Purpose of HAVING: Filters grouped aggregate summaries.

✓WHERE vs HAVING: WHERE filters rows BEFORE grouping; HAVING filters groups AFTER grouping.

✓HAVING with COUNT / SUM / AVG / MIN / MAX: Filter groups based on counts, totals, means, or limits.

✓Multiple Conditions: Combine aggregate checks using AND / OR.

Live Interactive — HAVING Lab

Experiment with WHERE (row filtering), GROUP BY (bucketing), and HAVING (group filtering) in real time.

1. WHERE (Filter Raw Rows)

2. GROUP BY (Form Buckets)

3. HAVING (Filter Groups)

9 Total Rows➔ WHERE ➔9 Filtered Rows➔ GROUP BY ➔4 Groups Formed➔ HAVING ➔2 Groups Kept

SELECT department,
       COUNT(*) AS employees,
       AVG(salary) AS avg_salary,
       SUM(sales) AS total_sales
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;

Group Filtering Visualizer2 of 4 Passed

📁 Sales (3 employees)✓ Passed HAVING

Condition: COUNT(*) >= 3 (Actual: 3) • Avg Sal: ₹65,000 • Total Sales: ₹375,000

• Aarav Sharma (Mumbai) — Sal: ₹65,000, Sales: ₹120,000

• Priya Patel (Delhi) — Sal: ₹58,000, Sales: ₹95,000

• Rohan Verma (Mumbai) — Sal: ₹72,000, Sales: ₹160,000

📁 Engineering (3 employees)✓ Passed HAVING

Condition: COUNT(*) >= 3 (Actual: 3) • Avg Sal: ₹91,667 • Total Sales: ₹120,000

• Sneha Iyer (Bengaluru) — Sal: ₹92,000, Sales: ₹40,000

• Vikram Singh (Bengaluru) — Sal: ₹88,000, Sales: ₹50,000

• Ananya Roy (Mumbai) — Sal: ₹95,000, Sales: ₹30,000

📁 HR (2 employees)✗ Filtered Out

Condition: COUNT(*) >= 3 (Actual: 2) • Avg Sal: ₹50,000 • Total Sales: ₹35,000

• Kabir Mehta (Delhi) — Sal: ₹48,000, Sales: ₹15,000

• Diya Nair (Mumbai) — Sal: ₹52,000, Sales: ₹20,000

📁 Marketing (1 employees)✗ Filtered Out

Condition: COUNT(*) >= 3 (Actual: 1) • Avg Sal: ₹60,000 • Total Sales: ₹85,000

• Neha Gupta (Delhi) — Sal: ₹60,000, Sales: ₹85,000

Final SQL Output Table2 Rows Returned

department	employees	avg_salary	total_sales
Sales	3	₹65,000	₹375,000
Engineering	3	₹91,667	₹120,000

🎯 Predict the Result Challenge (1 of 3)

Given the complete employees table (9 rows):

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;

Which groups will appear in the query output?

🎯 Knowledge Assessment: SQL HAVING

Test your conceptual understanding and reasoning with 8 practical questions.

1. What is the primary purpose of the SQL HAVING clause?

2. Which of the following statements about WHERE vs HAVING is correct?

3. Why does the query "SELECT department, COUNT(*) FROM employees WHERE COUNT(*) > 5 GROUP BY department;" produce a syntax error?

4. Consider a table with 4 departments having average salaries: Sales ($45k), Tech ($85k), HR ($42k), Ops ($65k). What does HAVING AVG(salary) > 50000 return?

5. What is the logical order of operations in a full SQL query containing both WHERE and HAVING?

6. In the clause "HAVING COUNT(*) > 5 AND AVG(salary) > 60000", which groups will appear in the final query output?

7. A table has 10 rows for "Electronics" with MAX(price) = 1500, and 5 rows for "Books" with MAX(price) = 45. What will "HAVING MAX(price) > 1000" keep?

8. What happens if a HAVING condition evaluates to false for all groups?