In SQL, Having clause is used to filter the records according to our requirements. Basically this clause applies the condition on a group which is created by using the “Group by” keyword.
In this tutorial we will learn to use the HAVING clause in SQL
Step 1- Employee Table
First of all, fetch all records of employee table
For that write select * from EMP and execute it.
Just consider the employee table below. Suppose, if we want to see which department is getting the salary greater than $2500, we will write a query containing Having clause.
Step 2-Preparing Query
Start by using the “select” keyword, followed by the name of the column is required to be filtered, which in this case is the Department Number.
After that, we will use the Max keyword which will return the highest value. In the parenthesis, we will enter the column name, from where we want to retrieve the highest value. Over here, we will get the data from the Salary column.
Now we need to mention from which table the data has to be retrieved from. For that we will use the “from” keyword followed by the name of the table which is EMP in our case.
Since we want to group the output according to the Department number over here, we will use the “group by” clause over here followed by the column name which is DEPTNO.
After that, we will use SQL Having clause to filter out the maximum salaries. Therefore after the Having clause, we will input a condition, that the max salary should be greater than $2500.
The complete query would be :
select deptno,MAX(sal)
from EMP
group by DEPTNO
having MAX(sal)>2500
Step 3- Execution
Let us execute the above written query. We will observe the output is according to the conditions we provided in the query, that is, it shows the salaries greater than 2450 and these are also grouped with the Department Number.
And that is how we can use Having clause in SQL.