136

How to Use HAVING in SQL

In this SQL page, you will learn what the HAVING keyword is and how to implement it in the Structured Query Language.

What is HAVING?

The HAVING is a keyword in SQL which selects the rows filtered by the GROUP BY keyword based on the particular single or multiple conditions.

It is used in SQL because the SQL software does not allow you to use the WHERE clause with their aggregate functions. In SQL, we can only use the HAVING clause in the SELECT query.

Syntax of HAVING clause

In the above syntax, the GROUP BY clause creates a group of the same rows, and the HAVING clause applies the conditions to the resultant groups. The HAVING clause performs its operation on the columns or fields of the table. If we cannot use the GROUP BY clause in the HAVING syntax, the HAVING clause works similarly to the SQL WHERE clause.

If you want to learn how to use the HAVING clause in the SQL table, then you have to follow the below steps:

1. Create the Simple Database and Table.
2. Insert the Data into the Table
3. View the Inserted Data without the HAVING clause.
4. Use the HAVING clause.

Step 1: Create the simple Database and Table

First, you have to create a new database in SQL.

The following query creates the Fortis_Hospital Database:

Now, you have to create the new table using the following CREATE TABLE syntax:

The following query creates the Doctor_Info table in the Fortis_Hospital Database:

Step 2: Insert the Data into the table

Now, you have to insert the data into the table using the following syntax:

The following query inserts the record of those doctors who work in Fortis_Hospital

Step 3: View the table’s data Without using the HAVING Clause

The following query shows the record of Doctors in an unsorted manner:

The output of the above SELECT query is shown below:

Step 4: Use the HAVING clause

The following SQL SELECT query shows the records of the table using the HAVING clause:

Output:

Doctor_Gender	SUM(Doctor_Salary)
Male	54000
Other	34000

HAVING Clause with SQL ORDER BY clause

We can also use the ORDER BY keyword with the HAVING clause in the SELECT statement of SQL.

Syntax of HAVING clause with ORDER BY clause

Example of HAVING clause with ORDER BY clause

The following query creates the new Subject table in the School database:

The following INSERT INTO query inserts the records into the Subject table:

The following query shows the records of the Subject table:

The below SQL query uses the SQL ORDER BY clause with the HAVING clause:

Output:

Subject_ID	COUNT(Student_ID) >=2
2208	2
2221	2
2248	3

HAVING clause with MIN function

We can also use the MIN aggregate function with the HAVING clause in Structured Query Language.

Syntax of Group BY clause with MIN function:

Example of MIN Aggregate Function with HAVING Clause

This example takes the below College_Stu_Details table to understand the concept of the HAVING clause with MIN aggregate function:

The following INSERT INTO statements insert the record of College students:

The following query simply shows the record of students in the tabular form on the screen:

The following query shows the minimum marks of a student in each subject from the above College_Stu_Details table:

Output:

Subject_ID	MIN (Stu_Marks)
English	65
Maths	92
Hindi	88
Computer	78

HAVING clause with MAX function

We can also use the MAX aggregate function with the HAVING clause in Structured Query Language.

Syntax of Group BY clause with MAX aggregate function:

Example of MAX aggregate Function with HAVING Clause

This example takes the below College_Stu_Details table to understand the concept of HAVING clause with SQL MAX aggregate function:

The following INSERT INTO statements insert the record of College students:

The following query simply shows the record of students in the tabular form on the screen:

The following query shows the maximum marks of a student in each subject from the above College_Stu_Details table:

---