MySQL HAVING

Summary: In this tutorial, you will learn how to use MySQL HAVING clause to specify a filter condition for a group of records or an aggregate.

Introducing MySQL HAVING clause

The MySQL HAVING clause is an optional part of and used only with the SQL SELECT statement. The MySQL HAVING clause specifies a filter condition for a group of record or an aggregate. The MySQL HAVING is often used with MySQL GROUP BY clause. When using with MYSQL GROUP BY clause, you can apply filter condition of the HAVING clause only to the columns appear in the GROUP BY clause. If the MySQL GROUP BY clause is omitted, the MySQL HAVING clause will behave like a WHERE clause. Notes that the MySQL HAVING clause applies to groups as a whole while the WHERE clause applies to individual rows.

Examples of MySQL HAVING clause

Let’s take a look at an example of using MySQL HAVING clause to have a better understanding.

We have orderDetails table in our sample database. We can use the MySQL GROUP BY clause to get all orders, number of items sold and total values in each order as follows:

Now you can ask what order has total value greater than $1000. In this case, you need to use the MySQL HAVING clause on aggregate to answer that question.

We use column alias for the aggregate sum(priceeach) as total so in the HAVING clause we just have to specify that column alias total instead of typing the aggregate sum(priceeach) again.

You can use a complex condition in the MySQL HAVING clause such as OR, AND operators. For example if you want to know what order has total value greater than $1000 and has more than 600 items in it. You can use the following query to find out:

The MySQL HAVING clause is useful only with the MySQL GROUP BY clause for building output of high-level reports. For example, you can use the MySQL HAVING clause to answer questions like how many order has total values more than 1000 this month, this quarter and this year?…

In this tutorial, you have learned how to use the MySQL HAVING clause together with the MySQL GROUP BY to specify filter condition on a group or aggregate.

Posts Related to MySQL HAVING

• MySQL GROUP BY

  Summary: In this tutorial, you will learn how to use MySQL GROUP BY to group selected records into a set of summary records by the ...

• Selecting Data with SQL IN

  Summary: In this tutorial, you will learn how to select a result set which their values match any one of a list of values by ...

• MySQL INNER JOIN

  Summary: In this tutorial, you will learn how to use MySQL INNER JOIN clause to select data from multiple tables based on join conditions. Introducing ...

• MySQL LEFT JOIN

  Summary: In this tutorial, you will learn how to use MySQL LEFT JOIN clause to retrieve data from more than one table. Introducing to MySQL ...

• Using MySQL SELECT Statement to Query Data

  Summary:In this tutorial, you will learn how to MySQL SELECT statement to query data from database tables. MySQL SELECT Statement Syntax In order to retrieve data from ...