HAVING Clause in MySQL | Filtering Grouped Data Using HAVING Clause

The WHERE clause filters individual records before grouping, whereas the HAVING clause filters groups after the GROUP BY clause has been applied. It is mainly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().

The HAVING clause is useful when we need to display only those groups that satisfy a particular condition. For example, a school may want to display only those classes having more than 40 students or departments whose average salary exceeds a specified amount.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Understand the purpose of the HAVING clause.
• Differentiate between WHERE and HAVING.
• Use HAVING with GROUP BY.
• Apply aggregate functions with HAVING.
• Write practical SQL queries using the HAVING clause.

---

What is HAVING Clause?

---

Sample Student Table

Roll_No	Name	Class	Section	Marks	Fee
101	Aarav	XI	A	91	45000
102	Diya	XI	A	84	45000
103	Kabir	XI	B	88	45000
104	Riya	XII	A	95	48000
105	Vivaan	XII	B	82	48000
106	Ananya	XII	B	90	48000

---

Basic Syntax

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

---

Example 1: Display Classes Having More Than Two Students

SELECT Class,
COUNT(*)
FROM Student
GROUP BY Class
HAVING COUNT(*) > 2;

Output

Class	COUNT(*)
XI	3
XII	3

---

Example 2: Display Classes Whose Average Marks Are Greater Than 88

SELECT Class,
AVG(Marks)
FROM Student
GROUP BY Class
HAVING AVG(Marks) > 88;

Output

Class	AVG(Marks)
XII	89.00

---

Example 3: Display Sections Having Total Fee Greater Than 90000

SELECT Section,
SUM(Fee)
FROM Student
GROUP BY Section
HAVING SUM(Fee) > 90000;

---

Difference Between WHERE and HAVING

WHERE	HAVING
Filters individual rows.	Filters groups of rows.
Executed before GROUP BY.	Executed after GROUP BY.
Cannot directly use aggregate functions.	Primarily used with aggregate functions.
Filters records.	Filters grouped results.

---

Execution Order of SQL Clauses

Step	Clause
1	SELECT
2	FROM
3	WHERE
4	GROUP BY
5	HAVING
6	ORDER BY

---

---

Using HAVING with Different Aggregate Functions

The HAVING clause can be used with all aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX(). It filters only those groups that satisfy the specified condition.

---

Example 1: Display Classes Having More Than One Student

SELECT Class,
       COUNT(*)
FROM Student
GROUP BY Class
HAVING COUNT(*) > 1;

This query displays only those classes that have more than one student.

---

Example 2: Display Sections Whose Average Marks Are Greater Than 85

SELECT Section,
       AVG(Marks)
FROM Student
GROUP BY Section
HAVING AVG(Marks) > 85;

---

Example 3: Display Classes Whose Total Fee Collection Is Greater Than 100000

SELECT Class,
       SUM(Fee)
FROM Student
GROUP BY Class
HAVING SUM(Fee) > 100000;

---

Example 4: Display Classes Whose Highest Marks Are Above 90

SELECT Class,
       MAX(Marks)
FROM Student
GROUP BY Class
HAVING MAX(Marks) > 90;

---

Example 5: Display Sections Whose Lowest Marks Are Less Than 85

SELECT Section,
       MIN(Marks)
FROM Student
GROUP BY Section
HAVING MIN(Marks) < 85;

---

Using WHERE and HAVING Together

The WHERE clause filters records before grouping, while the HAVING clause filters groups after grouping. Both clauses can be used together in the same query.

Example

Display the average marks of Class XII students only if the average marks are greater than 85.

SELECT Class,
       AVG(Marks)
FROM Student
WHERE Class='XII'
GROUP BY Class
HAVING AVG(Marks) > 85;

---

Comparison: WHERE vs HAVING

Feature	WHERE	HAVING
Purpose	Filters individual rows.	Filters grouped data.
Execution	Before GROUP BY	After GROUP BY
Aggregate Functions	Generally not used.	Used with aggregate functions.
Works On	Records	Groups
Common Use	Select required records.	Select required groups.

---

Solved Example 1

Display classes having more than two students.

Solution:

SELECT Class,
COUNT(*)
FROM Student
GROUP BY Class
HAVING COUNT(*) > 2;

---

Solved Example 2

Display sections whose average marks are greater than 85.

Solution:

SELECT Section,
AVG(Marks)
FROM Student
GROUP BY Section
HAVING AVG(Marks) > 85;

---

Solved Example 3

Display classes where the highest marks are greater than 90.

Solution:

SELECT Class,
MAX(Marks)
FROM Student
GROUP BY Class
HAVING MAX(Marks) > 90;

---

Solved Example 4

Display sections whose total fee collection is greater than 90000.

Solution:

SELECT Section,
SUM(Fee)
FROM Student
GROUP BY Section
HAVING SUM(Fee) > 90000;

---

Real-Life Applications

Organization	Requirement	Example
School	Classes having more than 40 students	HAVING COUNT(*) > 40
School	Sections with average marks above 75	HAVING AVG(Marks) > 75
Company	Departments with salary expenditure above a limit	HAVING SUM(Salary) > value
Hospital	Departments treating more than 100 patients	HAVING COUNT(*) > 100
Retail Store	Categories with sales above a target	HAVING SUM(Sales) > value
Bank	Branches with deposits exceeding a target amount	HAVING SUM(Deposit) > value

---

Common Errors

Mistake	Correct Practice
Using HAVING without GROUP BY when grouping is required.	Use HAVING after GROUP BY to filter grouped results.
Using WHERE to filter aggregate values.	Use HAVING for conditions involving aggregate functions.
Writing HAVING before GROUP BY.	The correct order is GROUP BY followed by HAVING.
Confusing WHERE with HAVING.	WHERE filters rows, whereas HAVING filters groups.
Using incorrect aggregate functions in the HAVING condition.	Select the aggregate function according to the requirement.

---

Interview Corner

---

---

Competency-Based Questions

1. A school wants to display only those classes that have more than 40 students. Write the SQL query using the HAVING clause.
2. Write an SQL query to display the sections whose average marks are greater than 75.
3. A company wants to display only those departments where the total salary exceeds ₹5,00,000. Write the SQL query using GROUP BY and HAVING.
4. Differentiate between the WHERE clause and the HAVING clause with suitable examples.
5. Explain why the HAVING clause is generally used with aggregate functions.

---

Multiple Choice Questions

1. The HAVING clause is used to:
   • (a) Sort records
   • (b) Filter grouped records
   • (c) Delete records
   • (d) Create tables
2. The HAVING clause is generally used with:
   • (a) INSERT
   • (b) GROUP BY
   • (c) UPDATE
   • (d) DELETE
3. Which clause filters records before grouping?
   • (a) WHERE
   • (b) HAVING
   • (c) ORDER BY
   • (d) DISTINCT
4. Which clause filters groups after grouping?
   • (a) WHERE
   • (b) HAVING
   • (c) SELECT
   • (d) FROM
5. Which query displays classes having more than two students?
   • (a) SELECT Class FROM Student WHERE COUNT(*) > 2;
   • (b) SELECT Class, COUNT(*) FROM Student GROUP BY Class HAVING COUNT(*) > 2;
   • (c) SELECT COUNT(*) FROM Student HAVING Class;
   • (d) SELECT GROUP BY Class;
6. Which aggregate function calculates the average value?
   • (a) SUM()
   • (b) AVG()
   • (c) MAX()
   • (d) COUNT()
7. Which SQL clause is executed after GROUP BY?
   • (a) WHERE
   • (b) HAVING
   • (c) FROM
   • (d) INSERT
8. Which of the following conditions is valid in the HAVING clause?
   • (a) HAVING AVG(Marks) > 80
   • (b) HAVING COUNT(*) > 5
   • (c) HAVING SUM(Fee) > 100000
   • (d) All of the above
9. Which clause is generally written after HAVING?
   • (a) FROM
   • (b) ORDER BY
   • (c) WHERE
   • (d) SELECT
10. Which statement about the HAVING clause is correct?
    • (a) It filters individual rows.
    • (b) It is used instead of GROUP BY.
    • (c) It filters grouped data based on conditions.
    • (d) It cannot be used with aggregate functions.

---

Quick Revision

Clause	Purpose
WHERE	Filters individual rows before grouping.
GROUP BY	Groups rows having similar values.
HAVING	Filters grouped results.
COUNT()	Counts records in each group.
SUM()	Calculates the total value of each group.
AVG()	Calculates the average value of each group.
MIN()	Returns the minimum value in each group.
MAX()	Returns the maximum value in each group.

---

Important Points to Remember

• The HAVING clause filters groups, not individual rows.
• It is generally used with the GROUP BY clause.
• Aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() are commonly used in the HAVING clause.
• The WHERE clause filters records before grouping, whereas the HAVING clause filters groups after grouping.
• The order of SQL clauses is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY.
• Every column in the SELECT list that is not an aggregate should normally be included in the GROUP BY clause.
• The HAVING clause helps generate meaningful summary reports by displaying only the required groups.
• HAVING is widely used in business reports, school databases, banking systems, hospitals, and retail applications.

---

CBSE Exam Tips

• Do not confuse the WHERE clause with the HAVING clause.
• Remember that WHERE filters rows, whereas HAVING filters groups.
• Practice SQL queries using GROUP BY together with HAVING.
• Revise the execution order of SQL clauses before the examination.
• Read competency-based questions carefully to determine whether the condition applies to rows or groups.
• Expect practical SQL-writing questions involving schools, companies, hospitals, banks, and retail stores.

---

Summary

The HAVING clause is used to filter grouped data after the GROUP BY clause has been applied. It works mainly with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX(). While the WHERE clause filters individual records before grouping, the HAVING clause filters the groups formed after aggregation. Mastering the HAVING clause enables you to create powerful SQL reports and solve a wide variety of CBSE examination questions confidently.

---

What's Next?

In the next chapter, you will learn the ORDER BY Clause in MySQL. You will understand how to sort query results in ascending and descending order, use multiple columns for sorting, and combine ORDER BY with WHERE, GROUP BY, and HAVING to produce well-organized reports.