How to use HAVING clause in SQL

In this article, we will learn about the HAVING clause concept and how to use it in SQL.

What is the HAVING clause?

In Structured Query Language, HAVING Clause used with GROUP BY Clause specifies the conditions that filter the results appearing in the output. It returns only those data from the group, which fulfills the conditions.

With the HAVING clause, we can use the WHERE clause also in the query. If we use Both Clauses together, WHERE clause will get executed first where it will filter the individual rows, then rows are grouped and at the end, HAVING Clause filters the groups.

HAVING clause conditions are placed after the GROUP BY clause. HAVING clause behaved same as WHERE clause in the Structured Query Language does not use the GROUP BY clause. We can use aggregate functions such as MIN, MAX, SUM, AVG, and COUNT. This function is only used with the SELECT clause and the HAVING clause.

Syntax of HAVING clause:

SELECT COLUMNS, AGGREGATE FUNCTION, FROM TABLENAME WHERE CONDITION GROUP BY COLUMN HAVING CONDITIONS; 

There are some steps we have to learn for how to use the HAVING clause in the SQL query:

1. Create a new database or use an existing database by selecting the database using the USE keyword followed by the database name.

2. Create a new table inside the selected database, or you can use an already created table.

3. If the table is created new, insert the records in the newly created database using the INSERT query and view the inserted data using the SELECT query without the HAVING clause.

4. Now, we are ready to use the HAVING clause in the SQL queries.

Step 1: Create a new database or use an already created database.

I have already created a database. I will use my existing created database name.

USE SCHOOL;

School is the database name.

Those who didn’t have created a database, follow the below query to create the database:

CREATE DATABASE database_name;

After creating the database, select the database using the USE keyword followed by the database name.

Step 2: Create a new table or use already existing table:

I have already created a table. I will use the existing table named Student.

To create the new tables follow below CREATE TABLE syntax:

CREATE TABLE table_name(

columnname1 datatype(column size),

columnname2 datatype(column size),

columnname3 datatype(column size)

);

Step 3: Insert the records in the newly created table using the INSERT query and view the records using the SELECT query.

Use below syntax to insert new records in the table:

INSERT INTO table_name VALUES(value1, value2, value3);

To view the records from the table using the below syntax:

SELECT * FROM table_name;

The following query will display the records of Employees

SELECT * FROM Student;

The output of the above SELECT query is:

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS	CHEMISTRY_MARKS	MATHS_MARKS	TOTAL_MARKS
1	NEHA	85	88	100	273
2	VISHAL	70	90	82	242
3	SAMKEET	75	88	96	259
4	NIKHIL	60	75	80	215
5	YOGESH	56	65	78	199
6	ANKITA	95	85	96	276
7	SONAM	98	89	100	287
8	VINEET	85	90	100	275
9	SANKET	86	78	65	229
10	PRACHI	90	80	75	245

Step 4: We are ready to use the HAVING clause in the Structured Query Language.

We will now take deep dive into the HAVING clause with the help of examples.

We have a table named Student that contains the following records.

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS	CHEMISTRY_MARKS	MATHS_MARKS	TOTAL_MARKS
1	NEHA	85	88	100	273
2	VISHAL	70	90	82	242
3	SAMKEET	75	88	96	259
4	NIKHIL	60	75	80	215
5	YOGESH	56	65	78	199
6	ANKITA	95	85	96	276
7	SONAM	98	89	100	287
8	VINEET	85	90	100	275
9	SANKET	86	78	65	229
10	PRACHI	90	80	75	245

Example 1: Write a query to display the sum of physics marks where the sum of physics marks is greater than 60 groups by student id.

SELECT STUDENT_ID, STUDENT_NAME, SUM(PHYSICS_MARKS) AS PHYSICS_MARKS FROM STUDENT GROUP BY STUDENT_ID  HAVING SUM(PHYSICS_MARKS) > 60;

In the above query, we have taken an aggregate function named SUM() followed by column name physics_marks, which will sum the column. First Sum(physics_marks) gets executed, then HAVING clause condition gets executed at the end, and the final result will be displayed. We have used the GROUP BY clause followed by the column name Student_Id to group the same values and consider them one group. If values aren't the same, no group will be formed for values. And at the end, we have used the HAVING clause where we put the condition that will help display only those student details where the sum of physics marks is greater than 60. If student physics marks are less than 60, it won't display the records.

The output of the above query is:

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS
1	NEHA	85
2	VISHAL	70
3	SAMKEET	75
6	ANKITA	95
7	SONAM	98
8	VINEET	85
9	SANKET	86
10	PRACHI	90

As we can see in the output, only those student ids, names, and physics marks are displayed where the sum of physics marks is greater than 60. As we used GROUP BY clause and no values are similar, they are counted as a single group.

Example 2: Write a query to display the maximum marks of chemistry marks where a maximum mark of chemistry marks is less than 90 groups by student id.

SELECT STUDENT_ID, STUDENT_NAME, MAX(CHEMISTRY_MARKS) AS CHEMISTRY_MARKS FROM STUDENT GROUP BY STUDENT_ID  HAVING MAX(CHEMISTRY_MARKS) < 90;  

In the above query, we have taken an aggregate function named MAX() followed by column name chemistry_marks, which will find the maximum marks of the column. We have used the GROUP BY clause followed by the column name Student_Id to group the same values and consider them one group. If values aren't the same, then a separate group will be formed for values. And at the end, we have used the HAVING clause where we put the condition that will help display only those student details where the maximum mark of chemistry marks is less than 90. If the student chemistry marks are greater than 90, it won't display the records. First, MAX(chemistry_marks) gets executed, then the HAVING clause condition gets executed at the end, and the final result will be displayed. The output of the above query is:

STUDENT_ID	STUDENT_NAME	CHEMISTRY_MARKS
1	NEHA	88
3	SAMKEET	88
4	NIKHIL	75
5	YOGESH	65
6	ANKITA	85
7	SONAM	89
9	SANKET	78
10	PRACHI	80

As we can see in the output, only those student ids, names, and chemistry marks are displayed where the maximum mark of chemistry marks is less than 90. As we used GROUP BY clause and no values are similar, they are counted as a single group.

Example 3: Write a query to display the mathematics marks where a minimum mark of mathematics marks is greater than 70 groups by student id.

SELECT STUDENT_ID, STUDENT_NAME, MIN(MATHS_MARKS) AS MATHS_MARKS FROM STUDENT GROUP BY STUDENT_ID  HAVING MIN(MATHS_MARKS) >70;

In the above query, we have taken an aggregate function named MIN() followed by column name maths_marks, which will find the minimum marks of the column. We have used the GROUP BY clause followed by the column name Student_Id to group the same values and consider them one group. If values aren't the same, then a separate group will be formed for values. And at the end, we have used the HAVING clause where we put the condition that will help display only those student details where the minimum mark of maths marks is greater than 70. If the Student's math marks are less than 70, it won't display the records. First, MIN(maths_marks) gets executed, then the HAVING clause condition gets executed at the end, and the final result will be displayed.

The output of the above query is:

STUDENT_ID	STUDENT_NAME	MATHS_MARKS
1	NEHA	100
2	VISHAL	82
3	SAMKEET	96
4	NIKHIL	80
5	YOGESH	78
6	ANKITA	96
7	SONAM	100
8	VINEET	100
10	PRACHI	75

As we can see in the output, only those student ids, names, and math marks are displayed where the minimum mark of math marks is greater than 70. As we used GROUP BY clause and no values are similar, they are counted as a single group.

Example 4: Write a query to display student details where minimum physics marks are greater than 56, AND maximum math marks are less than 98.

SELECT STUDENT_ID, STUDENT_NAME, MIN(PHYSICS_MARKS) AS PHYSICS_MARKS , MAX(MATHS_MARKS) AS MATHS_MARKS FROM STUDENT GROUP BY STUDENT_ID HAVING MIN(PHYSICS_MARKS) >58 AND MAX(MATHS_MARKS)<98;

We used double aggregate functions in a single query min() and max() in the above query. Min() is used to find out the minimum marks of physics, and Max() is used to find out the maximum math marks. First, the query will find the min() and max() marks of physics and math from the student table. As we used GROUP BY clause, so similar values mapped as one group, else values will be as separated. As no values are similar in the table, all values have separated. No values will be mapped as one group. Next, we used the HAVING clause, which works as WHERE clause difference only HAVING clause mapped into the group. First, the condition is MIN(PHYSICS_MARKS) > 58. As no values are similar, each value will be considered as minimum value, and compared with the condition, the same approach is used for MAX(MATHS_MARKS). As we used AND operator in the query, those conditions fulfill both conditions. Only those students' records are displayed in the final output.

The output of the above query is:

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS	MATHS_MARKS
2	VISHAL	70	82
3	SAMKEET	75	96
4	NIKHIL	60	80
6	ANKITA	95	96
9	SANKET	86	65
10	PRACHI	90	75

As we can see in the output, only those student records are displayed where minimum marks of physics values are greater than 56, AND maximum math marks are less than 98.

In the above example, if used OR operator instead of AND operator, then all the ten records are displayed because OR operator says if one condition fails and other conditions are true, then table records fulfill the conditions.