We often need to selectively display data after they have been aggregated by using GROUP BY clause. Often, people confuse it with the usage of WHERE clause. WHERE clause does not work for GROUP BY because WHERE clause controls what data are affected before GROUP BY taking effect. For example, WHERE sets criteria for a SELECT statement - after the qualified rows are retrieved, GROUP BY is applied to these rows. Then HAVING clause is applied to the grouped data to further restrict what grouped rows should be displayed.

To set criteria on what rows should be returned after GROUP BY is applied, we need to use HAVING clause.

When using HAVING clause, consider the following facts and guidelines:

1. HAVING clause can only be used when a query has GROUP BY clause within it.

2. HAVING clause is normally used together with aggregate functions such as count, sum, etc.

3. In a query, HAVING clause must be placed immediately after GROUP BY clause.

4. In a query, HAVING clause must be placed before an ORDER BY clause.

Practice #1: Using HAVING clause to selectively show aggregated data.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query returns a list of employees who have handled
more than 100 orders in all years. If HAVING clause is
removed, all employees will be returned with their corresponding
number of handled orders.
*/
select a.EmployeeID, 
    concat(b.FirstName, ' ', b.LastName) as "Name",
    count(a.OrderID) as "Num of Orders"
from orders as a
join employees as b on a.EmployeeID = b.EmployeeID
group by EmployeeID
having count(OrderID) > 100
order by "Num of Orders" desc

Query result set - 4 rows returned:

Practice #2: Using HAVING clause to selectively show aggregated data.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query returns a list of employees who have handled
more than 30 orders in each year.
*/
select year(OrderDate) as "Order Year",
    a.EmployeeID, 
    concat(b.FirstName, ' ', b.LastName) as "Name",
    count(a.OrderID) as "Num of Orders"
from orders as a
join employees as b on a.EmployeeID = b.EmployeeID
group by EmployeeID, YEAR(a.OrderDate)
having count(a.OrderID) > 30
order by "Order Year", "Num of Orders" desc

Query result set - 13 rows returned:

Other tutorials in this category

1. Using Aggregate Functions and Group By Clause in MySQL

2. More About GROUP BY Clause in MySQL

3. Summarizing Data From Multiple Tables in MySQL

4. Summarizing Data From Multiple Tables in MySQL - Part 2

Back to Tutorial Index Page