SQL HAVING Clause

The HAVING clause is used to filter groups created by the GROUP BY clause. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN().

While the WHERE clause filters individual rows before grouping, the HAVING clause filters groups after the grouping process.

---

Why Use the HAVING Clause?

• Filter grouped records.
• Apply conditions on aggregate values.
• Generate meaningful summary reports.
• Retrieve only the required groups.

---

Sample Table : Student

RollNo	Name	City	Marks
101	Amit	Jaipur	85
102	Neha	Delhi	92
103	Rohan	Jaipur	78
104	Priya	Delhi	88
105	Karan	Mumbai	81
106	Meera	Jaipur	91

---

Syntax of HAVING

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

---

Example 1: Cities Having More Than Two Students

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

Output

City	COUNT(*)
Jaipur	3

---

Example 2: Cities with Average Marks Greater Than 85

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

---

Example 3: Cities with Total Marks Greater Than 170

SELECT City,
       SUM(Marks)
FROM Student
GROUP BY City
HAVING SUM(Marks) > 170;

---

Example 4: Cities Where Highest Marks Are Above 90

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

---

Example 5: Cities Where Lowest Marks Are Less Than 80

SELECT City,
       MIN(Marks)
FROM Student
GROUP BY City
HAVING MIN(Marks) < 80;

---

Using WHERE and HAVING Together

The WHERE clause filters rows before grouping, while the HAVING clause filters the grouped results.

SELECT City,
       AVG(Marks)
FROM Student
WHERE Marks >= 80
GROUP BY City
HAVING AVG(Marks) > 85;

In this query:

• WHERE removes students scoring less than 80.
• GROUP BY groups the remaining records by city.
• HAVING displays only those cities whose average marks are greater than 85.

---

Difference Between WHERE and HAVING

WHERE	HAVING
Filters individual rows.	Filters groups.
Executed before GROUP BY.	Executed after GROUP BY.
Cannot use aggregate functions directly.	Can use aggregate functions.
Works on table records.	Works on grouped results.

---

Execution Order of a Query

FROM
   ↓
WHERE
   ↓
GROUP BY
   ↓
HAVING
   ↓
SELECT
   ↓
ORDER BY

Understanding this order helps in writing correct SQL queries.

---

Common Aggregate Functions Used with HAVING

Function	Example
COUNT()	HAVING COUNT(*) > 5
SUM()	HAVING SUM(Salary) > 100000
AVG()	HAVING AVG(Marks) > 80
MAX()	HAVING MAX(Marks) > 90
MIN()	HAVING MIN(Marks) < 50

---

Real-Life Applications

Situation	Example
Departments with more than 20 employees	HAVING COUNT(*) > 20
Cities with average sales above ₹50,000	HAVING AVG(Sales) > 50000
Classes with average attendance above 90%	HAVING AVG(Attendance) > 90
Branches with total revenue above ₹10 lakh	HAVING SUM(Revenue) > 1000000

---

Common Errors

Error	Reason
Using HAVING without GROUP BY (where grouping is intended)	HAVING is generally used after GROUP BY for grouped data.
Using aggregate functions in WHERE	Aggregate functions belong in HAVING.
Incorrect column name	Column does not exist.
Syntax Error	Incorrect SQL syntax.

---

Quick Revision

Concept	Remember
WHERE	Filters rows before grouping.
GROUP BY	Creates groups.
HAVING	Filters groups after grouping.
Aggregate Functions	Frequently used with HAVING.

---

CBSE Exam Tips

• Remember that HAVING is used with grouped data.
• Do not use aggregate functions such as AVG() or SUM() directly in the WHERE clause.
• Understand the execution order: WHERE → GROUP BY → HAVING.
• Practice queries that combine WHERE, GROUP BY, and HAVING.
• Read the question carefully to decide whether the condition applies to individual rows or grouped results.

---

Summary

The HAVING clause filters grouped records based on aggregate values. It works after the GROUP BY clause and is commonly used with functions such as COUNT(), SUM(), AVG(), MAX(), and MIN(). Understanding the difference between WHERE and HAVING is essential for solving CBSE Class 12 Informatics Practices SQL queries accurately.