Counting row

A database is often used to answer "how often does a certain type of data appear in a table?" The problem. For example, you may want to know how many pets you have, or how many pets each owner has, or you may want to conduct various types of census operations on your animals.

Calculate the total number of animals you own and "how many rows are there in the pet table?" The same question. Because each pet has a record, COUNT(*) calculates the number of rows, so the query for calculating animals is as follows:

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

Previously, you searched the names of people who owned pets. If you want to know how many pets each owner has, you can use COUNT():

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

The above query uses GROUP BY to group all records of each owner. It is very useful to use COUNT() in combination with GROUP BY to describe your data under various groups. The following example shows different methods for performing animal census operations.

Number of each animal:

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

Number of animals of each 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 gender is unknown.

Number of animals per species and sex combination:

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 |
+---------+------+----------+

When using COUNT(), you do not need to retrieve the entire table, for example, when the previous query is executed only on dogs and cats, as follows:

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 want the number of animals of each gender to apply only to animals of known gender:

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 specify the names of the columns to be selected outside the COUNT() value, there should be a GROUP BY clause to name those same columns, otherwise, the following occurs:

• If 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): In aggregated query without GROUP BY, expression
  #1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
  this is incompatible with sql_mode=only_full_group_by

• 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 uncertain, and the server is free to choose the value in any row:

  mysql> SET sql_mode = '';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> SELECT owner, COUNT(*) FROM pet;
  +--------+----------+
  | owner  | COUNT(*) |
  +--------+----------+
  | Harold |        8 |
  +--------+----------+

Previous: pattern matching