(mysql.info.gz) Counting rows

 
 3.3.4.8 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(*)' 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 |
      +--------+----------+
 
 Note the use of `GROUP BY' to group together all records for each
 `owner'.  Without it, all you get is an error message:
 
      mysql> SELECT owner, COUNT(*) FROM pet;
      ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
      with no GROUP columns is illegal if there is no GROUP BY clause
 
 `COUNT()' and `GROUP BY' are useful for characterizing your data in
 various ways.  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 known-sex
 animals:
 
      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 |
      +---------+------+----------+
Info Catalog
(mysql.info.gz) Pattern matching
(mysql.info.gz) Retrieving data
(mysql.info.gz) Multiple tables
automatically generated byinfo2html