Counting Rows

Counting Rows
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(*) [1303] 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() [1303] 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() [1303] 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 |
+------+----------+
... zobacz całą notatkę