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     -> 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 | +--------------+-------------+ 2 rows in set (0.00 sec)  

Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: