Mysql having

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
    -> GROUP BY order_date
     -> HAVING COUNT(*) > 1;
+------------+----------+
| order_date | COUNT(*) |
 +------------+----------+
| 2006-01-23 |        2 |
| 2006-02-02 |        2 |
+------------+----------+
2 rows in set (0.01 sec)

Similarly, the next example looks at the order_items table and finds which products have shipped at least 30 units. In this example, a column alias is used on the summary column and also in the HAVING clause.

mysql> SELECT product_code, SUM (quantity) as num_shipped
    -> FROM order_lines
    -> GROUP BY product_code
     -> HAVING num_shipped > 30;
+--------------+-------------+
| product_code | num_shipped |
+--------------+-------------+
 | MAXI         |          48 |
| MINI         |          36 |
+--------------+-------------+