HAVING Clause in SQL

HAVING Clause in SQL

In SQL, the HAVING clause is used to place conditions on groups created by the GROUP BY clause in a SELECT statement. Unlike the WHERE clause, which filters records before grouping, the HAVING clause filters records after grouping.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

Why Do We Need the HAVING Clause?

The HAVING clause is essential because WHERE cannot be used with aggregate functions like SUM, COUNT, AVG, MIN, and MAX. Both HAVING and WHERE are used for filtering, but they serve different purposes in SQL queries.

Difference Between HAVING and WHERE Clause

One of the most frequently asked interview questions in SQL is the difference between HAVING and WHERE clauses. The main difference is that WHERE filters data before grouping, whereas HAVING filters data after grouping.

Comparison Table:

Syntax of the HAVING Clause in SQL

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1
HAVING condition;

Examples of the HAVING Clause in SQL

Let’s explore four different examples that demonstrate the use of the HAVING clause with various aggregate functions.

Example 1: Using HAVING with SUM Function

Consider the following Sales table:

If you want to calculate the total sales amount for each city, use:

SELECT SUM(Amount), City FROM Sales GROUP BY City;

Output:

Now, suppose you want to display only those cities where the total sales exceed 6000. Use the HAVING clause:

SELECT SUM(Amount), City FROM Sales GROUP BY City HAVING SUM(Amount) > 6000;

Output:

Example 2: Using HAVING with COUNT Function

Consider the Students table:

To count the number of students by age, use:

SELECT COUNT(Roll_No), Age FROM Students GROUP BY Age;

Output:

Now, to display only ages where at least two students exist:

SELECT COUNT(Roll_No), Age FROM Students GROUP BY Age HAVING COUNT(Roll_No) >= 2;

Output:

Example 3: Using HAVING with MIN and MAX Functions

Consider the Employees table:

To find the minimum salary in each department:

SELECT MIN(Salary), Department FROM Employees GROUP BY Department;

Output:

Now, to display departments where the minimum salary is greater than 5000:

SELECT MIN(Salary), Department FROM Employees GROUP BY Department HAVING MIN(Salary) > 5000;

Output:

Example 4: Using HAVING with AVG Function

To find the average salary of employees in each department:

SELECT AVG(Salary), Department FROM Employees GROUP BY Department;

Output:

To filter only departments where average salary is more than 7000:

SELECT AVG(Salary), Department FROM Employees GROUP BY Department HAVING AVG(Salary) > 7000;

Output:

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Conclusion

The HAVING clause is crucial for filtering grouped data in SQL. Unlike WHERE, it allows the use of aggregate functions to refine query results after grouping. Mastering HAVING will significantly improve your SQL skills and data manipulation capabilities.

having clause in sql example
group by clause in sql
group by and having clause in sql
having clause in sql without group by
having clause in sql w3schools
having clause in sql oracle
where clause in sql
having clause example