The HAVING Clause is always used after the GROUP BY clause, it can not come without the GROUP BY clause. It works the same as the WHERE Clause, it is therefore also used to apply conditions on the aggregate_functions which was impossible by using simple WHERE clause.
The HAVING clause simply contains the conditions to filter data just like in WHERE clause.
SQL HAVING Syntax
SELECT column_name(s), aggregate_function(column_name) FROM TABLE WHERE some_condition GROUP BY grouping_condtion HAVING aggregate_function(column_name) operator VALUE
Consider the following table for this exercise
Users
| Firstname | Lastname | Salary | DeptID |
|---|---|---|---|
| John | Smith | 1000 | 1 |
| Mathew | Simon | 3000 | 1 |
| Bill | Steve | 2200 | 1 |
| Amanda | Rogers | 1800 | 2 |
| Steve | Hills | 2800 | 2 |
| Steve | jobs | 2400 | 2 |
| bill | cosby | 700 | 3 |
SELECT AVG(Salary) AS Average_Salary, DeptID FROM Users GROUP BY DeptID HAVING AVG(Salary) > 2000
Result of the Query
| Average_Salary | DeptID |
|---|---|
| 2066.66 | 1 |
| 2333.33 | 2 |
The data in the above example is first grouped using the Salary and DeptID column. The result is calculated using the AVG function and since the WHERE clause is not applicable here we use the HAVING Clause. The salary returned is those that meet the criteria which is above average of $2000.