MySQL: HAVING Clause

This tutorial explains how to use the MySQL HAVING clause with syntax and examples.

Description

The MySQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.

Syntax

The syntax for the HAVING Clause in MySQL is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

Parameters or Arguments

aggregate_function

A function such as SUM, COUNT, MIN, MAX, or AVG functions.

expression1, expression2, ... expression_n

The expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.

WHERE conditions

Optional. These are the conditions for the records to be selected.

HAVING condition

This is a further condition applied only to the aggregated results to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

Example - Using SUM function

Let's look at a MySQL HAVING clause example that uses the SUM function.

You could also use the SUM function to return the name of the product and the total quantity (for that product). The MySQL HAVING clause will filter the results so that only products with a total quantity greater than 10 will be returned.

SELECT product, SUM(quantity) AS "Total quantity"
FROM order_details
GROUP BY product
HAVING SUM(quantity) > 10;

Example - Using COUNT function

Let's look at how we could use the HAVING clause with the COUNT function in MySQL.

You could use the COUNT function to return the name of the product and the number of orders (for that product) that are in the 'produce' category. The MySQL HAVING clause will filter the results so that only products with more than 20 orders will be returned.

SELECT product, COUNT(*) AS "Number of orders"
FROM order_details
WHERE category = 'produce'
GROUP BY product
HAVING COUNT(*) > 20;

Example - Using MIN function

Let's next look at how we could use the HAVING clause with the MIN function in MySQL.

You could also use the MIN function to return the name of each department and the minimum salary in the department. The MySQL HAVING clause will return only those departments where the minimum salary is less than $50,000.

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary)  50000;

Example - Using MAX function

For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The MySQL HAVING clause will return only those departments whose maximum salary is greater than $25,000.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) > 25000;