Hit enter after type your search item
Home / SQL Tutorials / The HAVING clause in SQL

The HAVING clause in SQL

The SQL HAVING clause is generally used with the GROUP BY clause for specifying a condition for a group or aggregated data.

The WHERE clause is used for specifying the search condition, however, you cannot use aggregate functions with WHERE clause.

The HAVING clause can only be used with the SELECT statement.

I will show you using the HAVING SQL clause with and without the GROUP BY clause for specifying the conditional search.

An example of HAVING clause with aggregate function

Let me start an example of HAVING clause with the SUM aggregate function. We have an example table for storing the employee salaries paid every month.

In the SELECT statement, the SUM function is used for getting the total salary paid to each employee (in our first query).

In the second query, the HAVING clause is used to return only those employees whom sum of paid salary is greater than or equal to 5000:

Query:


SQL HAVING simple

The purpose of this example is showing that HAVING clause can also be used without GROUP BY clause, however, the result is not meaningful. Now let us use it with the GROUP BY clause.

Note: You may test this and subsequent quires in MS SQL Server (tested) and MySQL as well.

The example of HAVING with GROUP BY clause

In this query, the SUM of salaries for each employee is calculated by using GROUP BY clause. After the GROUP BY, the HAVING clause is used where aggregate SUM function is used for setting the condition to return only those records which total is greater than or equal to 2500:

The Query:


SQL HAVING GROUP BY

The example of using COUNT function with HAVING clause

The COUNT function gets the number of rows returns by the SELECT statement. You may use this with the HAVING clause for defining a condition for returning the aggregated result.

In this example, I have used two queries. The first with only GROUP BY clause that returns numbers of times salaries paid to each employee with their names. There, I used COUNT and SUM functions with GROUP BY clause.

In the second query, I used HAVING clause as well for returning only those records which count of paid salary is two or more.

The queries:


The result-sets:

SQL HAVING COUNT

You can see, the first query returned five employees records while the second only three records.

Using MAX function with HAVING clause

Similarly, you may use the MAX function with the HAVING clause. The following query returns the employee names and maximum salaries of employees who are paid more than 4500.

The query:


SQL HAVING MAX

You cannot specify this MAX expression in the WHERE clause.

This div height required for enabling the sticky sidebar