SQL

HAVING in SQL

🎯 The HAVING clause in SQL is used to filter grouped data after aggregate functions have been applied. It is commonly used with the GROUP BY clause to return only those groups that satisfy a specified condition. Unlike WHERE, which filters individual rows, HAVING filters groups.

📖 What is the HAVING Clause?

The HAVING clause allows you to specify conditions on aggregate values such as counts, sums, averages, minimums, and maximums. It is evaluated after rows have been grouped.

Information

Use WHERE to filter rows before grouping, and use HAVING to filter groups after aggregation.

📝 Basic Syntax

HAVING Syntax

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

📊 Sample Table

Consider the following Students table:

StudentID	Name	Department	City	Marks	FeesPaid
101	Alice	Computer Science	Chennai	92	50000
102	Bob	Mathematics	Coimbatore	85	45000
103	Charlie	Physics	Madurai	78	40000
104	David	Computer Science	Chennai	95	50000
105	Eva	Mathematics	Salem	88	47000

1️⃣ HAVING with COUNT()

Display departments that have more than one student.

HAVING with COUNT()

SELECT Department,
       COUNT(*) AS TotalStudents
FROM Students
GROUP BY Department
HAVING COUNT(*) > 1;

Result:

Department	TotalStudents
Computer Science	2
Mathematics	2

2️⃣ HAVING with SUM()

Find departments whose total fees collected exceed 90000.

HAVING with SUM()

SELECT Department,
       SUM(FeesPaid) AS TotalFees
FROM Students
GROUP BY Department
HAVING SUM(FeesPaid) > 90000;

3️⃣ HAVING with AVG()

Display departments whose average marks are greater than 85.

HAVING with AVG()

SELECT Department,
       AVG(Marks) AS AverageMarks
FROM Students
GROUP BY Department
HAVING AVG(Marks) > 85;

4️⃣ HAVING with MIN() and MAX()

Display departments whose highest marks exceed 90.

HAVING with MAX()

SELECT Department,
       MAX(Marks) AS HighestMarks
FROM Students
GROUP BY Department
HAVING MAX(Marks) > 90;

📍 HAVING with Multiple Conditions

You can combine multiple conditions using AND and OR.

Multiple HAVING Conditions

SELECT Department,
       COUNT(*) AS TotalStudents,
       AVG(Marks) AS AverageMarks
FROM Students
GROUP BY Department
HAVING COUNT(*) > 1
AND AVG(Marks) > 85;

🔍 HAVING with WHERE

The WHERE clause filters rows before grouping, while HAVING filters groups after aggregation.

WHERE and HAVING Together

SELECT Department,
       AVG(Marks) AS AverageMarks
FROM Students
WHERE Marks >= 80
GROUP BY Department
HAVING AVG(Marks) > 85;

Tip

Processing order:
1️⃣ WHERE filters rows.
2️⃣ GROUP BY creates groups.
3️⃣ Aggregate functions calculate results.
4️⃣ HAVING filters the groups.

📊 HAVING with ORDER BY

Use ORDER BY to sort grouped results after the HAVING clause.

HAVING with ORDER BY

SELECT Department,
       AVG(Marks) AS AverageMarks
FROM Students
GROUP BY Department
HAVING AVG(Marks) > 80
ORDER BY AverageMarks DESC;

🔗 HAVING with JOIN

The HAVING clause is frequently used with joins to filter aggregated data across related tables.

HAVING with JOIN

SELECT s.Name,
       COUNT(c.CourseID) AS TotalCourses
FROM Students s
JOIN Courses c
ON s.StudentID = c.StudentID
GROUP BY s.Name
HAVING COUNT(c.CourseID) >= 3;

⚖️ WHERE vs HAVING

Feature	WHERE	HAVING
Filters	Individual rows.
		Groups.
Uses Aggregate Functions	No.	Yes.
Execution Stage	Before grouping.	After grouping.
Common Usage	Filter raw data.	Filter summary data.

📊 Common Aggregate Functions with HAVING

Function	Purpose
COUNT()	Counts records.
SUM()	Calculates totals.
AVG()	Calculates averages.
MIN()	Returns the smallest value.
MAX()	Returns the largest value.

💼 Real-World Example

A university administrator wants a report showing only departments that have at least two students and an average mark above 85.

Department Performance Report

SELECT Department,
       COUNT(*) AS StudentCount,
       AVG(Marks) AS AverageMarks,
       SUM(FeesPaid) AS TotalFees
FROM Students
GROUP BY Department
HAVING COUNT(*) >= 2
AND AVG(Marks) > 85
ORDER BY AverageMarks DESC;

This query generates a summarized report while excluding departments that do not meet the specified criteria.

⚠️ Best Practices

Best Practice

Use HAVING only for conditions involving grouped data or aggregate functions. Use WHERE whenever possible to reduce the number of rows before grouping, combine HAVING with GROUP BY for reporting, and use meaningful aliases to improve query readability.

🚀 Key Points to Remember

📌 HAVING filters grouped data.
📌 It is commonly used with GROUP BY.
📌 It supports aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().
📌 WHERE filters rows before grouping, while HAVING filters groups after grouping.
📌 Multiple conditions can be combined using AND and OR.
📌 ORDER BY can be used to sort grouped results.

>>"The HAVING clause lets you filter summarized data, turning grouped results into meaningful insights."

Summary

✅ The HAVING clause is an essential SQL feature for filtering grouped and aggregated data. When combined with GROUP BY and aggregate functions, it enables powerful reporting, analytics, and business intelligence by allowing you to focus only on groups that meet specific conditions.