Difference between count () and sum () in Mysql

In mysql, there are two functions: count () and sum (). Many of them are confused. From an English perspective, we can find that count is the number of statistics, sum is the sum of values and can only be numeric.

In mysql, there are two functions: count () and sum (). Many of them are confused. From an English perspective, we can find that count is the number of statistics, sum is the sum of values and can only be numeric.

Requirement: query the average score of two or more non-pass students.
Two query statements are often used:

The Code is as follows:
1. select name, sum (score <60), avg (score) from result group by name having sum (score <60)> = 2;

Check again

Calculate the total number of animals you own and the number of rows in the pet table ?" This is the same problem because each pet has a record. The COUNT (*) function calculates the number of rows. Therefore, the query for the number of animals should be:

The Code is as follows:
Mysql> select count (*) FROM pet; + ---------- + | COUNT (*) | + ---------- + | 9 | + ---------- +

You have previously retrieved the name of a person with a pet. If you want to know how many pets each master has, you can use the COUNT () function:

The Code is as follows:
Mysql> SELECT owner, COUNT (*) FROM pet group by owner; + -------- + ---------- + | Owner | COUNT (*) | + -------- + ---------- + | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | + -------- + ---------- +

Note: If you use group by to GROUP all records of each owner without it, you will receive an error message:

The Code is as follows:
Mysql> SELECT owner, COUNT (*) FROM pet; ERROR 1140 (42000): Mixing of GROUP columns (MIN (), MAX (), COUNT (),...) With no GROUP columns is illegal if there is no group by clause

COUNT () and group by classify your data in various ways. The following example shows different methods for conducting an animal census.

Quantity of each animal:

The Code is as follows:
Mysql> SELECT species, COUNT (*) FROM pet group by species; + --------- + ---------- + | Species | COUNT (*) | + --------- + ---------- + | Bird | 2 | | Cat | 2 | | Dog | 3 | | Hamster | 1 | | Snake | 1 | + --------- + ---------- +

Number of animals for each gender:

The Code is as follows:
Mysql> SELECT sex, COUNT (*) FROM pet group by sex; + ------ + ---------- + | Sex | COUNT (*) | + ------ + ---------- + | NULL | 1 | | F | 4 | | M | 4 | + ------ + ---------- +

(In this output, NULL indicates "unknown gender ".)

Number of animals by type and gender:

The Code is as follows:
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, in the previous query, when only a dog or a cat is executed, it should be:

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

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

Mysql sum

The Code is as follows:
2. select name, count (score <60 )! = 0) as a, avg (score) from result group by name having a> = 2;