MySQL under count () and sum () difference

Requirements: Check out the average score of 2 and 2 people who fail.
There are two types of query statements that are often used:

The code is as follows	Copy Code
1. Select Name,sum (Score <), AVG (score) from result group by name has sum (score<60) >=2;

Look again

Count 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:

The code is as follows	Copy Code
Mysql> SELECT COUNT (*) from pet; +----------+ | COUNT (*) | +----------+ | 9 | +----------+

In the front, you retrieve 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:

The code is as follows	Copy Code
Mysql> SELECT owner, COUNT (*) from pet GROUP by owner; +--------+----------+ | Owner | COUNT (*) | +--------+----------+ |        Benny | 2 | |        Diane | 2 | |        Gwen | 3 | |        Harold | 2 | +--------+----------+

Note that by using GROUP by to group all records for each owner, you will get an error message without it:

The code is as follows	Copy Code
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 classify your data in various ways. The following examples show different ways of conducting animal census operations.

Number of animals per species:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
mysql> SELECT Sex, COUNT (*) from the pet GROUP by sex; +------+----------+ | sex | COUNT (*) | +------+----------+ |        NULL | 1 | |        f | 4 | |        m | 4 | +------+----------+

(in this output, null denotes "unknown sex.") ）

Number of animals by type and sex:

The code is as follows	Copy Code
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 | +---------+------+----------+

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:

The code is as follows	Copy Code
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-disaggregated animals known as sex:

code is as follows

copy code

Mysql> SELECT species, Sex, COUNT (*) from pet    -> WHERE sex are not NULL   &nb Sp -> 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 | +---------+------+----------+

MySQL sum

The code is as follows	Copy Code
2.select Name, Count ((score<60)!=0) as A,avg (score) from result group by name has a >=2;