SQL Having Clause

Having clause is used to filter data based on the group functions. If the SQL SELECT statement does not contain aggregate functions (max, min, sum, count), you can use a SQL SELECT statement that contains a HAVING clause without a GROUP BY clause.

The HAVING clause without a GROUP BY clause acts like the WHERE clause.

If the HAVING clause does not contains aggregate functions, use the WHERE clause for faster performance.

SQL Having Syntax:

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value;

Please Read the Previous Article for SQL-Table Structure 

SQL Having Examples:

Example1

SQL HAVING clause example that uses the SQL SUM function.
Use the SQL SUM function to return the name of the department and the total salary (in the department wise). The SQL HAVING clause will filter the results so that only departments with salary greater than 700000 (7 Lakh) will be returned.

SELECT Department, SUM(Salary) AS "Total Salary"

FROM Employee

GROUP BY Department

HAVING SUM(Salary) > 700000.00;

Query Result

 

Example2

HAVING clause with the SQL COUNT function.
Use the SQL COUNT function to return the name of the department and the number of employees (in the department wise) that make over 600000 (6 Lakh). The SQL HAVING clause will filter the results so that only departments with more than 1 employee will be returned.

SELECT Department, COUNT(*) AS "Number of employees"

FROM Employee

WHERE salary > 600000.00

GROUP BY Department

HAVING COUNT(*) > 1;

Query Result

 

Example3

HAVING clause with the SQL MIN function.
Use the SQL MIN function to return the name of each department and the minimum salary in the department. The SQL HAVING clause will return only those departments where the minimum salary is greater than 500000 (5 Lakh).

SELECT Department, MIN(Salary) AS "Lowest salary"

FROM Employee

GROUP BY Department

HAVING MIN(Salary) > 500000;

Query Result

Example4

HAVING clause with the SQL MAX function.

Use the SQL MAX function to return the name of each department and the maximum salary in the department. The SQL HAVING clause will return only those departments whose maximum salary is less than 1000000 (10 Lakh).

SELECT Department, MAX(salary) AS "Highest salary"

FROM Employee

GROUP BY Department

HAVING MAX(Salary) < 1000000;

Query Result