How to conditionally show data after they are grouped in MySQL

Using GROUP BY with HAVING Clause

In last tutorial, we have had a close look about how to use GROUP BY clause. In this tutorial, we are going to look at how to conditionally show data after they are grouped.

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:

HAVING clause can only be used when a query has GROUP BY clause within it.
HAVING clause is normally used together with aggregate functions such as count, sum, etc.
In a query, HAVING clause must be placed immediately after GROUP BY clause.
In a query, HAVING clause must be placed before an ORDER BY clause.

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

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
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:
HAVING clause restricts what aggregated rows can be displayed in the result set.

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

/*
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:
Using HAVING clause with GROUP BY.

Next tutorial: Summarizing Data From Multiple Tables
Back to Section Index: Summarize Data
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search