SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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_IDSTUDENT_NAMEPHYSICS_MARKSCHEMISTRY_MARKSMATHS_MARKSTOTAL_MARKS
1NEHA8588100273
2VISHAL709082242
3SAMKEET758896259
4NIKHIL607580215
5YOGESH566578199
6ANKITA958596276
7SONAM9889100287
8VINEET8590100275
9SANKET867865229
10PRACHI908075245

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_IDSTUDENT_NAMEPHYSICS_MARKSCHEMISTRY_MARKSMATHS_MARKSTOTAL_MARKS
1NEHA8588100273
2VISHAL709082242
3SAMKEET758896259
4NIKHIL607580215
5YOGESH566578199
6ANKITA958596276
7SONAM9889100287
8VINEET8590100275
9SANKET867865229
10PRACHI908075245

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_IDSTUDENT_NAMEPHYSICS_MARKS
1NEHA85
2VISHAL70
3SAMKEET75
6ANKITA95
7SONAM98
8VINEET85
9SANKET86
10PRACHI90
How To Use HAVING Clause In SQL

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_IDSTUDENT_NAMECHEMISTRY_MARKS
1NEHA88
3SAMKEET88
4NIKHIL75
5YOGESH65
6ANKITA85
7SONAM89
9SANKET78
10PRACHI80
How To Use HAVING Clause In SQL

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_IDSTUDENT_NAMEMATHS_MARKS
1NEHA100
2VISHAL82
3SAMKEET96
4NIKHIL80
5YOGESH78
6ANKITA96
7SONAM100
8VINEET100
10PRACHI75
How To Use HAVING Clause In SQL

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_IDSTUDENT_NAMEPHYSICS_MARKSMATHS_MARKS
2VISHAL7082
3SAMKEET7596
4NIKHIL6080
6ANKITA9596
9SANKET8665
10PRACHI9075
How To Use HAVING Clause In SQL

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.



ADVERTISEMENT
ADVERTISEMENT