Collection functions of MySQL advanced features (1)

So far, you have learned how to retrieve one or more records from a table based on specific conditions. However, if you want to perform data statistics on the records in a table. For example, if you want to count the results of a polls stored in the table. Or you want to know the average time a visitor spends on your site. Any type

So far, you have learned how to retrieve one or more records from a table based on specific conditions. However, if you want to perform data statistics on the records in a table. For example, if you want to count the results of a polls stored in the table. Or you want to know the average time a visitor spends on your site. Any type

So far, you have learned how to retrieve one or more records from a table based on specific conditions. However, if you want to perform data statistics on the records in a table. For example, if you want to count the results of a polls stored in the table. Or you want to know the average time a visitor spends on your site. To collect statistics on any type of data in the table, you must use a collection function. You can count the number of records, average value, minimum value, maximum value, or sum. When you use a set function, it returns only one number, which represents one of these statistical values.

The biggest feature of these functions is that they are often used in combination with group by statements. It is important to note that the set functions cannot be used together with non-grouped columns.

Row and column count

Calculates the number of records returned by the query statement.

Directly calculate the value of the function COUNT (*). For example, calculate the number of cats in the pet table:

Mysql> SELECT count (*) FROM pet WHERE species = 'cat ';

+ ---------- +

| Count (*) |

+ ---------- +

| 2 |

+ ---------- +

Count the number of Field Values

For example, calculate the number of species columns in the pet table:

Mysql> SELECT count (species) FROM pet;

+ ---------------- +

| Count (species) |

+ ---------------- +

| 9 |

+ ---------------- +

If the same type appears more than once, the type will be calculated multiple times. If you want to know the number of pets with a specific type, you can use the WHERE clause, as shown in the following example:

Mysql> select count (species) FROM pet WHERE species = 'cat ';

Note the result of this statement:

+ ---------------- +

| COUNT (species) |

+ ---------------- +

| 2 |

+ ---------------- +

In this example, the number of authors with the 'cat' type is returned. If the name appears twice in the pet table, the return value of the sub-function is 2. And it is consistent with the results of the statements mentioned above:

SELECT count (*) FROM pet WHERE species = 'cat'

In fact, these two statements are equivalent.

If you want to know how many different types of pets there are. You can obtain the number by using the DISTINCT keyword. For example:

Mysql> select count (DISTINCT species) FROM pet;

+ ------------------------- +

| COUNT (DISTINCT species) |

+ ------------------------- +

| 5 |

+ ------------------------- +

If the 'cat' type appears more than once, it is calculated only once. The DISTINCT keyword determines that only different values are calculated.

Generally, when you use COUNT (), the null values in the field are ignored.

In addition, the COUNT () function is usually used with the group by clause. For example, the number of each pet can be returned as follows:

Mysql> SELECT species, count (*) FROM pet group by species;

+ --------- + ---------- +

| Species | count (*) |

+ --------- + ---------- +

| Bird | 2 |

| Cat | 2 |

| Dog | 3 |

| Hamster | 1 |

| Snake | 1 |

+ --------- + ---------- +