Having Clause

Introduction

One fine day a company hires you as a developer. Your work is to write database queries. Now on the same day, your manager comes to you with a task to get all the department’s names having employees less than 50. 

You try your best to solve the problem and write a SQL query something like 

SELECT Dept FROM Table_Name GROUP BY Dept WHERE COUNT(*)<50;

But now you find out that the WHERE clause is not working with the GROUP BY clause. Because WHERE clause works on the whole table.

 

To solve this problem, let’s learn a new clause named HAVING and how to use it in our query.

The HAVING Clause 

The HAVING clause places the condition in the groups defined by the GROUP BY clause using the SELECT statement. It can only be used with the SELECT statements.

HAVING clause is preferred to use with the GROUP BY clause. Else it will work like a WHERE clause.

Syntax

The following code block will show how to use the HAVING clause in a query.

SELECT Column_Name FROM Table_Name GROUP BY Column_Name HAVING [conditions];

It is compulsory to use the GROUP BY clause before using the HAVING clause. 

 

Solution for the question which our manager gives us to solve.

SELECT Dept FROM EMPLOYEES GROUP BY Dept HAVING COUNT(*)<50;

Some more Examples

Let consider the EMPLOYEES table having all the employee data consisting of columns like EmployeeName, EmployeeAge, EmployeeSalary, Dept, and EmployeeID as the primary key( it is a set of values of the table, which uniquely defines each record in this table). 

 

Now from the given Database Table, 

 

We need to find all dept names where the numbers of employees are less than 3 and need to print the Dept column as a result.
SQL Query:

SELECT Dept  FROM EMPLOYEES  GROUP BY Dept  HAVING COUNT(*)<3;

Result for the above query:

 

Let us now discuss another scenario: 

We need to find the age group where the number of employees is less than 2 and need to print the EmployeeAge column as a result.

SQL Query:

SELECT EmployeeAge  FROM EMPLOYEES  GROUP BY EmployeeAge  HAVING COUNT(*)<2;

Result for the above query:

 

Another variation of the above query is as follows:

We need to find the age group where the number of employees is less than 2 and sort the output result in descending order and need to print the EmployeeAge column as a result.

SQL Query:

SELECT EmployeeAge  FROM EMPLOYEES  GROUP BY EmployeeAge  HAVING COUNT(*)<2 ORDER BY COUNT(*) DESC;

Result for the above query:

 

Let’s consider one more example,

We need to find those Dept name whose employee salary is minimum of that dept and also greater than 10000 and need to print the EmployeeSalary, Dept column as a result.

SQL Query

SELECT MIN(EmployeeSalary), Dept FROM EMPLOYEES GROUP BY Dept HAVING MIN(EmployeeSalary)>10000;

Result for the above query:

Aggregate functions with which HAVING clause can be used.

An aggregate function performs a calculation of one or more values and returns a single value. The aggregate function is often used in combination with the SELECT statement's GROUP BY clause and HAVING clause.

 

Let us now have a look at some functions:

1. AVG – to get the average of a set of data values. 

We need to find the average salaries of each department and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT AVG(EmployeeSalary), Dept FROM EMPLOYEES GROUP BY Dept HAVING AVG(EmployeeSalary);

Result for the above query:

 

2. COUNT – To count rows in a specified table. 

We need to find the count rows in each department and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT COUNT(EmployeeSalary), Dept FROM EMPLOYEES GROUP BY Dept HAVING COUNT(EmployeeSalary);

Result for the above query:

 

3. MIN – To get the minimum value in a set of data values. 

We need to find those Dept names whose employee salary is minimum of that dept and also greater than 10000 and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT MIN(EmployeeSalary), Dept FROM EMPLOYEES GROUP BY Dept HAVING MIN(EmployeeSalary)>10000;

Result for the above query:

 

4. MAX – To get the maximum value in a set of data values. 

We need to find those Dept names whose employee salary is maximum of that dept and also greater than 10000 and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT MAX(EmployeeSalary), Dept FROM EMPLOYEES GROUP BY Dept HAVING MAX(EmployeeSalary)>10000;

Result for the above query:

 

5. SUM – To calculate the sum of data values. 

We need to find the sum of salaries of each department and need to print the EmployeeSalary, Dept column as a result.

SQL Query:

SELECT SUM(EmployeeSalary), Dept FROM EMPLOYEES GROUP BY Dept HAVING SUM(EmployeeSalary);

Result for the above query: