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.

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.

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

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 –

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 -

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 -

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 –

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 –

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 –