MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 the
pet
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 use COUNT()
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 uses GROUP BY
to group
all records for each owner
. The use of
COUNT()
in conjunction with
GROUP 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 use
COUNT()
. 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 the
COUNT()
value, a GROUP
BY
clause should be present that names those same
columns. Otherwise, the following occurs:
If the
ONLY_FULL_GROUP_BY
SQL
mode is enabled, an error occurs:
mysql>SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec) mysql>SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
If ONLY_FULL_GROUP_BY
is
not enabled, the query is processed by treating all rows
as a single group, but the value selected for each named
column is nondeterministic. The server is free to select
the value from any row:
mysql>SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec) mysql>SELECT owner, COUNT(*) FROM pet;
+--------+----------+ | owner | COUNT(*) | +--------+----------+ | Harold | 8 | +--------+----------+ 1 row in set (0.00 sec)
See also Section 12.19.3, “MySQL Handling of GROUP BY”. See
Section 12.19.1, “Aggregate Function Descriptions” for information about
COUNT(
behavior and related optimizations.
expr
)