MySQL Having Clause

Previous Page
Next Page

The MySQL HAVING Clause is used to filter records with Aggregate functions and used with GROUP BY.

MySQL Having syntax

The basic syntax of a having clause is as follows:

SELECT <column_names>
FROM <table_name>
[WHERE <condition>]
GROUP BY <column_names>
HAVING <condition>
ORDER BY <column_names>;

MySQL 'Having' with an aggregate function in Select Statement

We can filter records using the having clause and have the aggregate function in the select clause.

SELECT AVG(REPLACEMENT_COST),RATING
FROM FILM
GROUP BY RENTAL_DURATION
HAVING RENTAL_DURATION >= 3;

MySQL Having clause with the where clause

We can use both the where and the having clause. We need to have an aggregate function somewhere in the query to be able to use the Having clause.

SELECT AVG(REPLACEMENT_COST),RATING
FROM FILM
where RATING IN ('PG','G')
GROUP BY RENTAL_DURATION
HAVING RENTAL_DURATION >= 3;

MySQL Having clause with an aggregate function

We can use the aggregate function in the Having clause itself.

SELECT P.CUSTOMER_ID,FIRST_NAME,LAST_NAME,EMAIL
FROM PAYMENT P,CUSTOMER C
WHERE P.CUSTOMER_ID=P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
HAVING MAX(P.AMOUNT) > (SELECT AVG(AMOUNT) FROM PAYMENT)
LIMIT 20;

Previous Page
Next Page