SQL HAVING Clause

In this tutorial, we will understand the HAVING clause concept in SQL with the help of examples.

The HAVING Clause in the SQL is used as we cannot use the WHERE clause condition with the aggregate function in the queries. The HAVING clause filters the result of GROUP BY based on the specified expression. The HAVING clause work is the same as the WHERE clause work helps to apply the expression in the query, but the HAVING clause works with the groups. To filter the group result, we use the HAVING clause.

Some important points to remember before using the HAVING clause:

1. The HAVING clause is used with the SELECT query.
2. In the query, the HAVING clause is mentioned after the GROUP BY clause.
3. In the query, the ORDER BY clause is mentioned at the end after the HAVING conditions
4. We use the HAVING Clause when we have a huge record.
5. The HAVING clause applies the filter on the data according to the condition mentioned in the query.

The syntax of the HAVING clause is as follows:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4, Function_Name(Column_Name) FROM Table_Name GROUP BY Column_Name HAVING condition;

The function name is the aggregate function we will use in the query. For example, SUM(), MIN(), MAX(), COUNT(), AVG().

Consider the already existing table, which has the following data:

Table Name: D_Students

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202111	Vaishnavi Patil	94	91	88	85	95	92	91	1
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Axar Patel	85	80	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	80	75	78	80	76	77	5
202118	Sakshi Patil	80	78	74	78	80	77	78	2
202119	Sopan Bhore	70	68	75	75	80	80	75	2
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202221	Anuja Wanare	85	88	86	82	84	85	85	5
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	75	1
202224	Aakash Jain	80	75	72	74	85	80	78	4
202225	Akshay Agarwal	85	80	78	88	90	82	84	5
202226	Shwetali Bhagwat	90	80	85	88	90	80	86	1
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5
202229	Manthan Koli	85	75	84	78	82	80	81	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

Example 1: Execute a query to display the student's information for a similar department id count greater than 3.

SELECT * FROM D_Students GROUP BY Department_Id HAVING COUNT(Department_Id)> 3;

We displayed the student's information whose department id count is similar, and the count is greater than 3 grouped by student_name in the above query.

The output of the above query is as follows:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202111	Vaishnavi Patil	94	91	88	85	95	92	91	1
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202114	Sonali Patole	95	90	92	88	92	90	91	4
202117	Mahesh Kumbhar	75	80	75	78	80	76	77	5

Example 2: Execute a query to display students’ information where total minimum values are greater than 80 groups by student name.

SELECT * FROM D_Students GROUP BY Student_Name HAVING MIN(Total) > 80;

We displayed the student's information where the total column minimum value is greater than 80 groups by Student_Name in the above query.

The output of the above query is as follows:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202225	Akshay Agarwal	85	80	78	88	90	82	84	5
202221	Anuja Wanare	85	88	86	82	84	85	85	5
202115	Axar Patel	85	80	82	86	92	84	85	1
202229	Manthan Koli	85	75	84	78	82	80	81	2
202230	Mayur Jain	80	88	87	90	92	90	88	1
202227	Mayuri Wagh	80	80	85	80	82	85	82	4
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202226	Shwetali Bhagwat	90	80	85	88	90	80	86	1
202114	Sonali Patole	95	90	92	88	92	90	91	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202111	Vaishnavi Patil	94	91	88	85	95	92	91	1
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202113	Yash Dhull	90	88	94	87	85	90	89	3

Example 3: Execute a query to display student’s information where the sum of the first semester to the sixth-semester column is greater than 500 groups by Student_Name.

SELECT * FROM D_Students GROUP BY Student_Name HAVING SUM(First_Sem + Second_Sem + Third_Sem + Fourth_Sem + Fifth_Sem + Sixth_Sem) > 500;

We display the student's information where the sum of the first semester to the sixth-semester column is greater than 500 groups by Student_Name in the above query.

The output of the above query is as follows:

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202225	Akshay Agarwal	85	80	78	88	90	82	84	5
202221	Anuja Wanare	85	88	86	82	84	85	85	5
202115	Axar Patel	85	80	82	86	92	84	85	1
202230	Mayur Jain	80	88	87	90	92	90	88	1
202226	Shwetali Bhagwat	90	80	85	88	90	80	86	1
202114	Sonali Patole	95	90	92	88	92	90	91	4
202228	Utkarsh Rokade	85	80	80	90	84	84	84	5
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202111	Vaishnavi Patil	94	91	88	85	95	92	91	1
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202113	Yash Dhull	90	88	94	87	85	90	89	3