Summary -

In this topic, we described about the HAVING Clause with detailed example.

HAVING clause is used to fetch Group of rows or values which are matched with the Specified condition in the Having clause. WHERE clause cannot work on aggregate functions so we can use having clause to work on aggregate function to restrict group of rows. HAVING clause used only in SELECT statement with GROUP BY clause.

Syntax -

SELECT column1, column2, aggregate function(column3), ……..
FROM Table_name
[ WHERE condition ]
 GROUP BY column1
 HAVING condition 
 [ ORDER BY column1]; 
  • column1, column2 – Specifies the column names that are used to fetch from table.
  • aggregate function – Specifies Aggregate function like COUNT( ), AVG( ).
  • column3 – Specifies the column name that are to perform Aggregate calculation.

Example –

Let us consider the employee_details and department_details tables are as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

Scenario – Get total number of employees in each department whose are having atleast 2 employees.

Requirement – Fetch dept_id, total number of employees in each department that are having more than 2 employees. The query was as follows –

SELECT dept_id, COUNT (emp_id) AS 'Total Employees' 
FROM employee_details
GROUP BY dept_id HAVING COUNT (emp_id) > 2; 

By executing above query, we can get results as shown below –

dept_id Total employees
1000 5
2000 4

Scenario – Get total salary of each designation whose salary is greater than 1000 in descending order.

Requirement – Fetching total salary of each designation in employee_details table whose salary is greater than 1000 in ascending order of designation. The query was as follows –

SELECT designation,SUM(salary) AS 'total salary' 
FROM employee_details
GROUPBY designation HAVING sum(salary) > 1000 ORDER BY designation; 

By executing above query, we can get results as shown below -

Total salary designation
38000.00 Analyst
15000.00 Clerk
85000.00 Director
52000.00 Manager
27000.00 Salesman

Scenario – Get total number of employees in each designation except one designation.

Requirement – Fetch Designation, Total number of employees in each designation which is having at least two number of employees except "Director". The query was as follows –

SELECT designation,COUNT(emp_id) AS 'total employees'
FROM employee_details
WHERE designation <> 'DIRECTOR' 
GROUP BY designation HAVING COUNT (emp_id) = 2; 

By executing above query, we can get results as shown below -

Total employees designation
2 Analyst
2 Manager
2 Salesman

Scenario – Get maximum salary in each department whose salary less than 50000.

Requirement – Fetch dept_id, maximum salary of each department whose salary less than 50000 from employee table. The query was as follows –

SELECT dept_id, MAX(salary) AS "Highest salary"
FROM employee_details GROUP BY dept_id
HAVING MAX(salary) < 50000; 

By executing above query, we can get results as shown below –

highest salary dept_id
45000.00 1000
40000.00 2000

Scenario – Get minimum salary in each department whose salary greater than 9000.

Requirement – Fetch dept_id, minimum salary of department whose salary greater than 9000 from employee_details table. The query was as follows –

SELECT dept_id, MIN(salary) AS "Lowest salary"
FROM employee_details 
GROUP BY dept_id
HAVING MIN(salary) > 9000; 

By executing above query, we can get results as shown below –

lowest salary dept_id
15000.00 1000
13000.00 2000

Scenario – Get total number of employees in sales department.

Requirement - Fetch total employees the corresponding department name where the department should have minimum 2 employees. The query was as follows –

SELECT COUNT (employee_details.emp_id) AS "total employees",
department_details.dept_name
FROM employee_details INNER JOIN department_details
ON employee_details.dept_id = department_details.dept_id
GROUP BY dept_name 
HAVING COUNT(employee_details.emp_id) > 2; 

By executing above query, we can get results as shown below –

Total employees Dept_name
5 Office
4 sales