SQL HAVING Clause

The HAVING clause in SQL is used to filter query results based on aggregate functions. Unlike the WHERE clause, which filters individual rows before grouping, the HAVING clause filters groups of data after aggregation. It is commonly used with functions like SUM(), AVG(), COUNT(), MAX(), and MIN().

In this article, we will learn the concept of the HAVING clause, and its syntax, and provide several practical examples.

What is the SQL HAVING Clause ?

The HAVING clause is used to filter the result of the GROUP BY statement based on the specified conditions. It allows filtering grouped data using Boolean conditions (AND, OR). It was introduced because the WHERE clause cannot be used with aggregate functions. Similar to WHERE clause, it helps apply conditions but specifically works with grouped data. When we need to filter aggregated results, the HAVING clause is the appropriate choice.

Key Features of the HAVING Clause

Used to filter grouped data based on aggregate functions.
Works with Boolean conditions (AND, OR
Cannot be used without GROUP BY unless an aggregate function is present.
Must be placed after the GROUP BY clause and before the ORDER BY clause (if used).
Helps generate summary reports from large datasets.

Syntax

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

Here, the function_name is the name of the function used, for example, SUM(), and AVG().

SQL HAVING Clause Examples

Here first we create a database name as “Company”, then we will create a table named “Employee” in the database. After creating a table we will execute the query.

Query

-- Create the Employee table with appropriate data types
CREATE TABLE Employee (
  EmployeeId int,
  Name varchar(50),
  Gender varchar(10),
  Salary int,
  Department varchar(20),
  Experience int -- Changed to int for years of experience
);

-- Insert multiple rows into the Employee table in a single query
INSERT INTO Employee (EmployeeId, Name, Gender, Salary, Department, Experience)
VALUES 
  (5, 'Priya Sharma', 'Female', 45000, 'IT', 2),
  (6, 'Rahul Patel', 'Male', 65000, 'Sales', 5),
  (7, 'Nisha Gupta', 'Female', 55000, 'Marketing', 4),
  (8, 'Vikram Singh', 'Male', 75000, 'Finance', 7),
  (9, 'Aarti Desai', 'Female', 50000, 'IT', 3);

SELECT * FROM Employee;

Output

Output

Example 1 : Using HAVING to Filter Aggregated Results

This employee table will help us understand the HAVING Clause. It contains employee IDs, Name, Gender, department, and salary. To Know the sum of salaries, we will write the query:

Query

SELECT Department, sum(Salary) as Salary
FROM Employee
GROUP BY department;

Output

Example 1.1

Now if we need to display the departments where the sum of salaries is 50,000 or more. In this condition, we will use the HAVING Clause.

Query

SELECT Department, sum(Salary) as Salary
FROM Employee
GROUP BY department
HAVING SUM(Salary) >= 50000;

Output

Example 1.2

Example 2: Using HAVING with Multiple Conditions

If we want to find the departments where the total salary is greater than or equal to $50,000, and the average salary is greater than $55,000. We can use the HAVING clause to apply both conditions.

Query

SELECT Department, SUM(Salary) AS Total_Salary, AVG(Salary) AS Average_Salary
FROM Employee
GROUP BY Department
HAVING SUM(Salary) >= 50000 AND AVG(Salary) > 55000;

Output

Department	Total_Salary	Average_Salary
Finance	75000	75000
Sales	65000	65000

Example 3: Using HAVING with COUNT()

If we want to find departments where there are more than two employees. For this, we can use the COUNT() aggregate function along with the HAVING clause.

Query

SELECT Department, COUNT(EmployeeId) AS Employee_Count
FROM Employee
GROUP BY Department
HAVING COUNT(EmployeeId) >= 2;

Output

Department	Employee_Count
IT	2

This query counts the number of employees in each department and uses the HAVING clause to filter for departments with more than two employees.

Example 4: Using HAVING with AVG()

In this example, let’s find out the average salary for each department and use the HAVING clause to display only those departments where the average salary is greater than $50,000.

Query

SELECT Department, AVG(Salary) AS Average_Salary
FROM Employee
GROUP BY Department
HAVING AVG(Salary) > 50000;

Output

Department	Average_Salary
Finance	75000
Marketing	55000
Sales	65000

HAVING vs WHERE

HAVING	WHERE
In the HAVING clause it will check the condition in group of a row.	In the WHERE condition it will check or execute at each row individual.
HAVING clause can only be used with aggregate function.	The WHERE Clause cannot be used with aggregate function like Having
Priority Wise HAVING Clause is executed after Group By.	Priority Wise WHERE is executed before Group By.

Conclusion

The HAVING clause is an essential tool in SQL for filtering results based on aggregated data. Unlike the WHERE clause, which applies conditions to individual rows, HAVING works on groups of data that have been aggregated using functions like SUM(), AVG(), and COUNT(). Understanding how and when to use the HAVING clause allows you to perform more complex data analysis and generate meaningful insights from your datasets.