Selecting Only Groups with Certain Characteristics

7.10.1 Problem

You want to calculate group summaries, but display the results only for those groups that match certain criteria.

7.10.2 Solution

Use a HAVING clause.

7.10.3 Discussion

You're familiar with the use of WHERE to specify conditions that individual records must satisfy to be selected by a query. It's natural, therefore, to use WHERE to write conditions that involve summary values. The only trouble is that it doesn't work. If you want to identify drivers in the driver_log table who drove more than three days, you'd probably first think to write the query like this:

mysql> SELECT COUNT(*), name
 -> FROM driver_log
 -> WHERE COUNT(*) > 3
 -> GROUP BY name;
ERROR 1111 at line 1: Invalid use of group function

The problem here is that WHERE specifies the initial constraints that determine which rows to select, but the value of COUNT( ) can be determined only after the rows have been selected. The solution is to put the COUNT( ) expression in a HAVING clause instead. HAVING is analogous to WHERE, but it applies to group characteristics rather than to single records. That is, HAVING operates on the already-selected-and-grouped set of rows, applying additional constraints based on aggregate function results that aren't known during the initial selection process. The preceding query therefore should be written like this:

mysql> SELECT COUNT(*), name
 -> FROM driver_log
 -> GROUP BY name
 -> HAVING COUNT(*) > 3;
+----------+-------+
| COUNT(*) | name |
+----------+-------+
| 5 | Henry |
+----------+-------+

When you use HAVING, you can still include a WHERE clausebut only to select rows, not to test summary values.

HAVING can refer to aliases, so the previous query can be rewritten like this:

mysql> SELECT COUNT(*) AS count, name
 -> FROM driver_log
 -> GROUP BY name
 -> HAVING count > 3;
+-------+-------+
| count | name |
+-------+-------+
| 5 | Henry |
+-------+-------+

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net