Movatterモバイル変換

Databases are often used to answer the question,“How often does a certain type of data occur in a table?” For example, you might want to know how many pets you have, or how many pets each owner has, or you might want to perform various kinds of census operations on your animals.

Counting the total number of animals you have is the same question as“How many rows are in thepet table?” because there is one record per pet.COUNT(*) counts the number of rows, so the query to count your animals looks like this:

mysql> SELECT COUNT(*) FROM pet;+----------+| COUNT(*) |+----------+|        9 |+----------+

Earlier, you retrieved the names of the people who owned pets. You can useCOUNT() if you want to find out how many pets each owner has:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;+--------+----------+| owner  | COUNT(*) |+--------+----------+| Benny  |        2 || Diane  |        2 || Gwen   |        3 || Harold |        2 |+--------+----------+

The preceding query usesGROUP BY to group all records for eachowner. The use ofCOUNT() in conjunction withGROUP BY is useful for characterizing your data under various groupings. The following examples show different ways to perform animal census operations.

Number of animals per species:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;+---------+----------+| species | COUNT(*) |+---------+----------+| bird    |        2 || cat     |        2 || dog     |        3 || hamster |        1 || snake   |        1 |+---------+----------+

Number of animals per sex:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;+------+----------+| sex  | COUNT(*) |+------+----------+| NULL |        1 || f    |        4 || m    |        4 |+------+----------+

(In this output,NULL indicates that the sex is unknown.)

Number of animals per combination of species and sex:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;+---------+------+----------+| species | sex  | COUNT(*) |+---------+------+----------+| bird    | NULL |        1 || bird    | f    |        1 || cat     | f    |        1 || cat     | m    |        1 || dog     | f    |        1 || dog     | m    |        2 || hamster | f    |        1 || snake   | m    |        1 |+---------+------+----------+

You need not retrieve an entire table when you useCOUNT(). For example, the previous query, when performed just on dogs and cats, looks like this:

mysql> SELECT species, sex, COUNT(*) FROM pet       WHERE species = 'dog' OR species = 'cat'       GROUP BY species, sex;+---------+------+----------+| species | sex  | COUNT(*) |+---------+------+----------+| cat     | f    |        1 || cat     | m    |        1 || dog     | f    |        1 || dog     | m    |        2 |+---------+------+----------+

Or, if you wanted the number of animals per sex only for animals whose sex is known:

mysql> SELECT species, sex, COUNT(*) FROM pet       WHERE sex IS NOT NULL       GROUP BY species, sex;+---------+------+----------+| species | sex  | COUNT(*) |+---------+------+----------+| bird    | f    |        1 || cat     | f    |        1 || cat     | m    |        1 || dog     | f    |        1 || dog     | m    |        2 || hamster | f    |        1 || snake   | m    |        1 |+---------+------+----------+

If you name columns to select in addition to theCOUNT() value, aGROUP BY clause should be present that names those same columns. Otherwise, the following occurs:

See alsoMySQL Handling of GROUP BY. SeeAggregate Function Descriptions for information aboutCOUNT(expr) behavior and related optimizations.