Filtering Summary Data

In Lesson 4, “Filtering and Sorting Data,” you learned how to filter data using a WHERE clause and a condition that references a table column. You cannot reference a column produced by an aggregate function in a WHERE clause. Instead, you must use HAVING.

The HAVING Clause

The HAVING clause must appear after the GROUP BY clause. It contains a conditional expression that can reference the result of an aggregate function in the query.

The following query finds dates on which more than one order was placed. It groups data from the orders table by order_date and uses a HAVING clause on the COUNT(*) aggregate to find where that group is made up of more than one table row.

mysql> SELECT order_date, COUNT(*)
    -> FROM orders

Get Sams Teach Yourself MySQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.