MySQL HAVING Clause

The HAVING clause can be used as a filter on a GROUP BY clause. It is used to apply a filter to a group of rows or aggregates. This is in contrast to the WHERE clause, which is applied before the GROUP BY clause.

Consider the following example.

SELECT last_name, COUNT(*) 

FROM sakila.actor

GROUP BY last_name

HAVING COUNT(*) > 3;

Result:

In the above example, we use the HAVING clause to filter the result set to only those records that have a count of greater than three (i.e. HAVING COUNT(*) > 3).

If we didn't use the HAVING CLAUSE, it would have returned all records — regardless of their count. Like this:

Another Example

Here's a slightly more advanced example that uses an INNER JOIN to retrieve data from two tables, as well as a SUM() function to add up the totals that each customer has paid.

SELECT  c.customer_id AS 'Customer ID', 

c.first_name AS 'First Name', 

c.last_name AS 'Last Name', 

       SUM(amount) AS 'Total Paid'

FROM sakila.payment p

INNER JOIN customer c ON

p.customer_id = c.customer_id

GROUP BY c.customer_id

HAVING SUM(amount) > 180

ORDER BY SUM(amount) DESC;

Result:

Here, we use the HAVING clause to return only those records where the total amount returned by the SUM() function exceeds 180.

We also sort this amount in descending order using the ORDER BY clause (so that the customer who's spent the most is listed at the top).

The examples on this page use the Sakila sample database.