SQL HAVING clause with examples.
The SQL HAVING Clause is mostly combination with GROUP BY Clause to restrict the result set that get from groups of returned rows to only those whose the condition will be TRUE.
Syntax for SQL HAVING Clause is:
SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables WHERE conditions GROUP BY expression1, expression2, ... expression_n HAVING condition;
· aggregate_function is a function such as SQL SUM function, SQL MIN function, or SQL MAX function.
· expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.
· condition is the condition that is used to restrict the groups of returned rows.
EXAMPLE - USING SUM FUNCTION
SQL HAVING clause example that uses the SQL SUM function.
SELECT department, SUM(salary) AS "Total salary " FROM Employee GROUP BY department HAVING SUM(salary) > 1000;
SQL SUM functions to return the name of the department and the total salary (in the associated department). The SQL HAVING clause will filter the results so that only departments with sales greater than 1000 will be returned.
EXAMPLE - USING COUNT FUNCTION
The HAVING clause with the SQL COUNT function.
SELECT department, COUNT(*) AS "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department HAVING COUNT(*) > 10;
SQL COUNT functions to return the name of the department and the number of employees (in the associated department) that make over 25,000.
EXAMPLE - USING MIN FUNCTION
We could use the HAVING clause with the SQL MIN function.
SQL MIN functions to return the name of each department and the minimum salary in the department.
SELECT department, MIN(salary) AS "Minimum salary" FROM employees GROUP BY department HAVING MIN(salary) > 35000;