Usage of Count,group by in MySQL (reproduced)

Calculate the total number of animals you own and "How many rows are there in the pet table?" Is the same problem because each pet has a record. The count (*) function calculates the number of rows, so the query for calculating the number of animals should be:

Mysql> SELECT COUNT (*) from pet;
+----------+
| COUNT (*) |
+----------+
| 9 |
+----------+
In the front, you searched for the name of the person who owns the pet. If you want to know how many pets each owner has, you can use the count () function:

Mysql> SELECT owner, COUNT (*) from the pet GROUP by owner;
+--------+----------+
| Owner | COUNT (*) |
+--------+----------+
|        Benny | 2 |
|        Diane | 2 |
|        Gwen | 3 |
|        Harold | 2 |
+--------+----------+
Note that using GROUP by to group all records for each owner, without it, you get the error message:

Mysql> SELECT owner, COUNT (*) from pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN (), MAX (), COUNT (),...)
With no group columns are illegal if there is no GROUP BY clause
COUNT () and group by categorize your data in a variety of ways. The following examples show different ways of carrying out animal census operations.

Number of animals per species:

Mysql> SELECT species, COUNT (*) from the 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 the pet GROUP by sex;
+------+----------+
| sex | COUNT (*) |
+------+----------+
|        NULL | 1 |
|        f | 4 |
|        m | 4 |
+------+----------+
(in this output, null means "unknown gender.") ）

Number of animals combined by type and gender:

mysql> SELECT species, Sex, COUNT (*) from the 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 |
+---------+------+----------+
If you use COUNT (), you do not have to retrieve the entire table. For example, the previous query, when only for dogs and cats, should be:

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 only need to know the number of sex-based animals of known sex:

Mysql> SELECT species, Sex, COUNT (*) from pet
   , WHERE sex was 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 |
+---------+------+----------+

http://zousheng.blog.163.com/blog/static/345363920108270207705/