The SQL HAVING clause is similar to SQL WHERE clause. The difference is that the SQL HAVING clause is used in aggregate functions but the SQL WHERE clause cannot be used in aggregate functions.
SQL HAVING clause Syntax
SELECT ColumnName1, ColumnName2, Aggregate_function (ColumnName) FROM TableName WHERE conditions GROUP BY ColumnName1, ColumnName2 HAVING Aggregate_function (ColumnName) operator value
Note: The Aggregate_function could be SQL MAX, MIN, SUM, COUNT or AVG.
SQL HAVING Clause Example
Table: Employees
| EmployeeId | FirstName | LastName | Department | Salary |
|---|---|---|---|---|
| 203 | Mazojys | Fxoj | Finance | 78000 |
| 204 | Jozzh | Lnanyo | Finance | 45800 |
| 205 | Syllauu | Dfaafk | Finance | 57000 |
| 206 | Gecrrcc | Srlkrt | Finance | 62000 |
| 302 | Jssme | Bdnaa | Development | 75000 |
| 303 | Dnnaao | Errllov | Development | 55000 |
| 304 | Tyoysww | Osk | Development | 49000 |
Select departments which has more than 3 employees:
SELECT Department, COUNT(EmployeeId) FROM Employees GROUP BY Department HAVING COUNT(EmployeeId)>3
The result will look like:
| Department | COUNT(EmployeeId) |
|---|---|
| Finance | 4 |
Example 2
Select departments whose minimum salary is more than $46,000:
SELECT Department, MIN(Salary) FROM Employees GROUP BY Department HAVING MIN(Salary)>46000
The result will look like:
| Department | MIN(Salary) |
|---|---|
| Development | 49000 |
I want to say that The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
i am a sql beginner and really like your blog and articles. haven’t posted anything but had to say thanks
Hi, sqlfan. This is my first visit to your blog, I have found your blog link through webhosting talk forum.
I have read some of your blog post not get what clearly it is as I am not that much technical. You have done lot of hard work and really you are sql fan. A nice informative sql tutorial. Keep it up.
I had never heard of the HAVING clause. Previously I would simply string multiple WHERE clauses together to get the desired task done, but it really didn’t look pretty and I suppose it would put more strain on the database?
The thing is nobody actually uses the HAVING clause these days and there aren’t many situations where you would actually have to use it.
I haven’t really used this HAVING clause. Actually I haven’t heard of it until now. But anyway, thanks for posting this very informative blog. I might use it next time.
HAVING clause has pretty much been ignored for that past 10 years, except maybe in highly specialized applications.
SQL HAVING, more like SQL WANTING.