"Getting Started with MySQL", we saw the following query, which tells us how many jokes are stored in our Jokes table:
mysql>SELECT COUNT(*) FROM Jokes; +----------+ | COUNT(*) | +----------+ | 4 | +----------+
The MySQL function COUNT used in this query belongs to a special class of functions called summary functions or group-by functions, depending on where you look. A complete list of these functions is provided in Chapter 6 of the MySQL Manual and in "MySQL Functions". Unlike other functions, which affect each entry in the result of the SELECT query individually, summary functions group together all the results and return a single result. In the above example, for instance, COUNT returns the total number of result rows.
Let's say you wanted to display a list of authors with the number of jokes they have to their names. Your first instinct, if you've paid attention, might be to retrieve a list of all the authors' names and ID's, then use COUNT to count the number of results when you SELECT the jokes with each author's ID. The PHP code, without error handling, for simplicity, would look something like this:
// Get a list of all the authors
$authors = mysql_query( 'SELECT Name, ID FROM Authors' );
// Process each author
while ($author = mysql_fetch_array($authors)) {
$name = $author['Name'];
$id = $author['ID'];
// Get count of jokes attributed to this author
$result = mysql_query(
"SELECT COUNT(*) AS NumJokes FROM Jokes WHERE AID='$id'" );
$row = mysql_fetch_array($result);
$numjokes = $row['NumJokes'];
// Display the author & number of jokes
echo("<p>$name ($numjokes jokes)</p>");
}
Note the use of AS in the second query above to give a friendlier name (NumJokes) to the result of COUNT(*).
This technique will work, but will require n+1 separate queries (where n is the number of authors in the database). Having the number of queries rely on a number of entries in the database is always something we want to avoid, as a large number of authors would make this script unreasonably slow and resource-intensive! Fortunately, another advanced feature of SELECT comes to the rescue!
If you add a GROUP BY clause to a SELECT query, you can tell MySQL to group the results of the query into sets that have the same value in the column(s) you specify. Summary functions like COUNT then operate on those groups—not on the entire result set as a whole. The next single query, for example, lists the number of jokes attributed to each author in the database:
mysql>SELECT Authors.Name, COUNT(*) AS NumJokes
->FROM Jokes, Authors
->WHERE AID = Authors.ID
->GROUP BY AID;
+------------+----------+
| Name | NumJokes |
+------------+----------+
| Kevin Yank | 3 |
| Joan Smith | 1 |
+------------+----------+
If we group the results by author ID (AID), we receive a breakdown of results for each author. Note that we could have specified GROUP BY Authors.ID and achieved the same result (since, as stipulated in the WHERE clause, these columns must be equal). GROUP BY Authors.Name would also work in most cases, but, as you can't guarantee that two different authors won't have the same name, in which case their results would be lumped together, it's best to stick to the ID columns, which are guaranteed to be unique for each author.