HAVING Clause in SQL

The HAVING clause in SQL is used to filter groups of rows created by the GROUP BY clause. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() when we need to apply a condition to grouped data.

In this article, we will learn what the HAVING clause is, its syntax, how it works, how to use HAVING with aggregate functions, and the difference between WHERE and HAVING. We will also see practical SQL examples to understand how the HAVING clause is used in real-world queries.

What is HAVING in SQL?

The HAVING clause is a filtering clause used to filter groups after the GROUP BY operation has been performed. It is mainly used when we want to apply a condition to an aggregated or grouped result.

Syntax

The basic syntax of the HAVING clause is:

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

Example

Let us take an example to understand how the HAVING clause works:

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5;

Explanation

In this example, we use the COUNT() function to count the number of employees in each department. The GROUP BY clause groups the employees based on their department, and the HAVING clause filters these groups. The condition HAVING COUNT(*) > 5 returns only those departments that contain more than five employees.

How HAVING Works in SQL

To understand the HAVING clause properly, it is important to understand how SQL processes a query step by step. In a query that uses WHERE, GROUP BY, HAVING, and ORDER BY, each clause has a different role.

Consider the following example:

SELECT department, COUNT(*) AS total_employees FROM employees WHERE salary > 30000 GROUP BY department HAVING COUNT(*) >= 3 ORDER BY total_employees DESC;

Suppose the employees table contains employees from different departments with different salaries. The query first selects the required data, removes unwanted rows, creates groups, counts the rows in each group, filters those groups, and finally sorts the result.

Let's understand each step.

Step 1: FROM

First, SQL gets the data from the employees table.

FROM employees

At this stage, SQL considers all the rows available in the table.

Step 2: WHERE

Next, the WHERE clause filters the individual rows.

WHERE salary > 30000

This means employees whose salary is 30,000 or less are removed from the result. Only employees with a salary greater than 30,000 continue to the next step.

Step 3: GROUP BY

After filtering the rows, SQL groups the remaining employees according to their department.

GROUP BY department

For example, all remaining employees from the IT department are placed into one group, employees from HR into another group, and so on.

Step 4: COUNT()

Now SQL counts the employees in each department.

COUNT(*)

The result may look like this:

These values represent the number of employees in each department after the WHERE condition has been applied.

Step 5: HAVING

Now the HAVING clause filters the groups based on their calculated count.

HAVING COUNT(*) >= 3

This means SQL keeps only those departments that have at least 3 employees.

From the previous result:

The HR group is removed because it has only two employees.

The remaining result is:

Step 6: ORDER BY

Finally, SQL sorts the remaining groups according to the number of employees.

ORDER BY total_employees DESC

DESC means descending order, so the department with the highest number of employees appears first.

The final result is:

Understanding the Complete Flow

The complete process can be remembered as:

FROM

 ↓

WHERE

  ↓

GROUP BY

 ↓

COUNT()

  ↓

HAVING

 ↓

ORDER BY

In simple words:

FROM gets the data → WHERE filters rows → GROUP BY creates groups → aggregate functions calculate values → HAVING filters groups → ORDER BY sorts the final result.

This is the main reason HAVING is different from WHERE. The WHERE clause filters individual rows, while the HAVING clause filters groups after they have been created and calculated.

HAVING with GROUP BY

The HAVING clause is most commonly used with the GROUP BY clause. GROUP BY combines rows that have the same value into separate groups. After the groups are created, HAVING is used to filter them based on a given condition.

For example, we can group employees by department and then use HAVING to show only the departments that have more than two employees.

For example:

SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department HAVING COUNT(*) > 2;

Suppose the table contains:

Output:

The HR and Sales groups are removed because they do not contain more than two employees.

SQL documentation similarly demonstrates HAVING as a way to eliminate groups after GROUP BY.

HAVING with COUNT()

The COUNT() function is commonly used with HAVING when we want to filter groups based on the number of rows they contain.

Example:

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) >= 5;

Explanation:

In this example, we use the COUNT() function to count the number of employees in each department. After that, the GROUP BY clause groups the employees based on their department. Finally, the HAVING clause returns only those departments that have at least five employees.

HAVING with SUM()

The SUM() function is used to find the total value of a numeric column for each group. After calculating the total, we can use the HAVING clause to keep only those groups that meet a specific condition.

Example:

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department HAVING SUM(salary) > 200000;

Explanation:

In this example, we use the SUM() function to calculate the total salary of employees in each department. After that, the employees are grouped by department. Finally, the HAVING clause returns only those departments where the combined salary is greater than 200,000.

HAVING with AVG()

The AVG() function is used to calculate the average value for each group. The HAVING clause can then be used to filter groups based on that average.

Example:

SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department HAVING AVG(salary) > 60000;

Explanation:

In this example, we use the AVG() function to calculate the average salary of each department. After that, the employees are grouped according to their department. Finally, the HAVING clause returns only those departments where the average salary is greater than 60,000.

HAVING with MIN()

The MIN() function is used to find the smallest value within each group. We can use HAVING to filter groups based on that minimum value.

Example:

SELECT department, MIN(salary) AS minimum_salary FROM employees GROUP BY department HAVING MIN(salary) > 30000;

Explanation:

In this example, we use the MIN() function to find the lowest salary in each department. After that, the employees are grouped by department. Finally, the HAVING clause returns only those departments where the lowest salary is greater than 30,000.

HAVING with MAX()

The MAX() function is used to find the largest value within each group. It can be used with HAVING to filter groups based on their highest value.

Example:

SELECT department, MAX(salary) AS maximum_salary FROM employees GROUP BY department HAVING MAX(salary) > 100000;

Explanation:

In this example, we use the MAX() function to find the highest salary in each department. After that, the employees are grouped by department. Finally, the HAVING clause returns only those departments where at least one employee has a salary greater than 100,000.

HAVING Without GROUP BY

The HAVING clause is usually used with GROUP BY, but it can also be used without GROUP BY in many SQL systems. In this case, the complete result is treated as a single group.

Example:

SELECT COUNT(*) AS total_employees FROM employees HAVING COUNT(*) > 10;

Explanation:

In this example, we use the COUNT() function to count the total number of employees. Since there is no GROUP BY clause, all employees are treated as one group. Finally, the HAVING clause checks whether the total number of employees is greater than 10. If the condition is true, the result is returned.

Difference Between GROUP BY and HAVING

GROUP BY and HAVING are closely related, but they have different purposes.

SQL HAVING Example Using Sales Data

Suppose we have a sales table with the following data:

To find categories where the total sales are greater than 50,000, we can use the SUM() function with the HAVING clause.

SELECT category, SUM(amount) AS total_sales FROM sales GROUP BY category HAVING SUM(amount) > 50000;

Output

Explanation:

In this example, we use the SUM() function to calculate the total sales for each category. After that, the GROUP BY clause groups the sales according to their category. Finally, the HAVING clause returns only those categories where the total sales are greater than 50,000.

Both Electronics and Furniture meet the condition, so both categories are included in the result.

SQL HAVING Example Using Student Data

Suppose we have a students table containing student marks:

To find courses where the average marks are greater than 75, we can use the AVG() function with the HAVING clause.

SELECT course, AVG(marks) AS average_marks FROM students GROUP BY course HAVING AVG(marks) > 75;

Output

Explanation:

In this example, we use the AVG() function to calculate the average marks for each course. After that, the GROUP BY clause groups the students according to their course. Finally, the HAVING clause returns only those courses where the average marks are greater than 75. The SQL course has an average of 83.33, so it is included in the result. The Java course has an average of 71.67, so it is not included.

Conclusion

The HAVING clause in SQL is an important part of SQL queries that work with grouped and aggregated data. It allows us to filter groups based on calculated values such as total sales, average salary, number of orders, minimum price, or maximum marks.