SQL Having

The HAVING clause, often overshadowed by its counterpart WHERE clause, plays a distinct role in SQL queries. Unlike WHERE, HAVING is employed specifically with the GROUP BY clause, enabling the filtering of grouped results based on aggregate functions.

Key Differences: HAVING vs. WHERE?

WHERE Clause:

• Primarily used for filtering records in a SQL query.
• Applied before any groupings are made.
• Compatible with SELECT, UPDATE, DELETE statements.

HAVING Clause:

• Filters grouped data based on aggregate functions.
• Executed after the GROUP BY clause.
• Applied in conjunction with the SELECT statement.

Syntax and Samples

SELECT column_name1, column_name2, ..., column_nameN, aggregate_function_name(column_name)
FROM table_name
GROUP BY column_name1
HAVING condition;

Example 1 : Counting Students by Age

Consider the Student_details table:

To count students by age, the SQL query would be:

SELECT COUNT(ID), Age
FROM Student_details
GROUP BY Age;

And to filter ages with a roll number greater than or equal to 2:

SELECT COUNT(ID), Age
FROM Student_details
GROUP BY Age
HAVING COUNT(ID) >= 2;

Exploring Aggregate Functions: MIN, MAX, and AVG

MIN Function with HAVING Clause

To showcase each department and its minimum salary:

SELECT MIN(Salary), Dept
FROM Employee_Details
GROUP BY Dept
HAVING MIN(Salary) > 4000;

MAX Function with HAVING Clause

To list each department and its maximum salary:

SELECT MAX(Salary), Dept
FROM Employee_Details
GROUP BY Dept
HAVING MAX(Salary) < 8000;

AVG Function with HAVING Clause

To find the average salary in each department:

SELECT AVG(Salary), Dept
FROM Employee_Details
GROUP BY Dept
HAVING AVG(Salary) > 6500;