SQL -Structured Query Language - Sql Clause - Having Clause Tutorial

' HAVING ' clause is used to filter out the column with aggregate functions like count, sum, avg, min, max. It is done based on groups created by the GROUP BY Clause.

' WHERE ' clause can't be used to filter the column with an aggregate function. It is only used to filter normal columns.

Syntax-

The basic syntax of the HAVING clause with aggregate functions like count, sum, avg, min, max.

SELECT function_name(column_name),..,column_name_group 
FROM 
table_name 
GROUP BY 
column_name_group 
HAVING 
aggregate_function_condition;

Here function_name can be count, sum, avg, min, max, and column_name_group is the name of the column to be group by.

aggregate_function_condition is the condition based on aggregate function like count, sum, avg, min, max.

For example –

SELECT COUNT(employee_id) 
FROM 
employees 
GROUP BY department_id 
HAVING COUNT(employee_id)>5;

In this statement, it will retrieve the number of employees in each department, whose department has employees greater than 5.

SELECT MAX(salary) 
FROM 
employees 
GROUP BY department_id 
HAVING MAX(salary)>8000;

In this statement, it will find the maximum salary of the employee in each department, whose department maximum salary is greater than 8000.

SELECT MIN(salary) 
FROM 
employees 
GROUP BY department_id 
HAVING MIN(salary)>8000;

In this statement, it will Find the minimum salary of the employee in each department, whose department minimum salary is greater than 8000.

SELECT AVG(salary) 
FROM 
employees 
GROUP BY department_id 
HAVING AVG(salary)<8000;

In this statement, it will Calculate the average salary of the employee in each department. whose department average salary is less than 8000.

SELECT SUM(salary) 
FROM 
employees 
GROUP BY department_id 
HAVING SUM(salary)<20000;

In this statement, it will Calculate the sum of the salary of the employee in each department. whose department salary sum is less than 20000.

Run Query