لطفا منتظر باشید ...
"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.
