This SQL tutorial explains how to use the SQL HAVING clause with syntax and examples.
The SQL HAVING clause is used in aggregate with the GROUP BY clause to preclude the corporations of returned rows to solely these whose the situation is TRUE.
The syntax for the HAVING clause in SQL is:
SELECT expression1, expression2, ... expression_n, aggregate_function (aggregate_expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n HAVING condition;
Parameters or Arguments
expression1, expression2, … expression_n Expressions that are now not encapsulated within an combination feature and should be covered in the GROUP BY Clause near the end of the SQL statement. aggregate_function This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions. aggregate_expression This is the column or expression that the aggregate_function will be used on. tables The tables that you wish to retrieve data from. There ought to be at least one desk listed in the FROM clause. WHERE conditions Optional. These are the stipulations for the information to be selected. HAVING condition This is a further circumstance utilized only to the aggregated outcomes to avoid the groups of lower back rows. Only these organizations whose situation evaluates to TRUE will be included in the result set.
Example – Using SUM function
Let’s look at a SQL HAVING clause instance that makes use of the SQL SUM function.
You ought to also use the SQL SUM function to return the name of the branch and the complete sales (in the related department). The SQL HAVING clause will filter the results so that only departments with sales higher than $1000 will be returned.
SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000;
Example – Using COUNT function
Let’s seem at how we ought to use the HAVING clause with the SQL COUNT function.
You should use the SQL COUNT function to return the name of the department and the variety of personnel (in the related department) that make over $25,000 / year. The SQL HAVING clause will filter the outcomes so that solely departments with extra than 10 employees will be returned.
SELECT department, COUNT(*) AS "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department HAVING COUNT(*) > 10;
Example – Using MIN function
Let’s subsequent appear at how we ought to use the HAVING clause with the SQL MIN function.
You could also use the SQL MIN function to return the name of each branch and the minimum earnings in the department. The SQL HAVING clause will return only these departments the place the minimum earnings is greater than $35,000.
SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) > 35000;
Example – Using MAX function
SQL MAX function
For example, you could additionally use the SQL MAX characteristic to return the title of each branch and the most income in the department. The SQL HAVING clause will return solely these departments whose maximum revenue is less than $50,000.
SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department HAVING MAX(salary) < 50000;