Collection function of MySQL advanced features _ MySQL

Collection function bitsCN.com of MySQL Advanced features

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 of 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 cat 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 |

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

Calculate the average value of a field

Calculate the average value of these values. Using the AVG () function, you can return the average value of all values in a field.

Assume that you conduct a complex public opinion survey on your site. Visitors can vote between 1 and 10, indicating that they like your site. You save the voting result in the INT type field named vote. To calculate the average value of your user's vote, you need to use the AVG () function ():

Select avg (vote) FROM opinion

The return value of this SELECT statement indicates your average liking for your site. The AVG () function can only be used for numeric fields. This function also ignores null values when calculating the average value.

A practical example is given. for example, to calculate the average age of each animal in the pet table, use the AVG () function and the group by clause:

Mysql> SELECT species, AVG (CURDATE ()-birth) FROM pet group by species;

The returned result is:

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

| Species | AVG (CURDATE ()-birth) |

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

| Bird | 1, 34160 |

| Cat | 74959.5 |

| Dog | 1, 112829.66666667 |

| Hamster | 1, 19890 |

| Snake | 1, 49791 |

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

Calculate the sum of field values

Assume that your website has been used to sell a certain product for two months. it is time to calculate the amount of money it has earned. Assume that a table named orders is used to record order information of all visitors. To calculate the SUM of all the purchase quantities, you can use the SUM () function ():

Select sum (purchase_amount) FROM orders

The return value of SUM () indicates the SUM of all values in the field purchase_amount. The data type of the field purchase_amount may be DECIMAL, but you can also use the SUM () function for other numeric fields ().

Using an inappropriate example, we calculate the total age of the same type of pets in the pet table:

Mysql> SELECT species, SUM (CURDATE ()-birth) FROM pet group by species;

You can view the result and compare it with the previous example:

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

| Species | SUM (CURDATE ()-birth) |

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

| Bird | 1, 68320 |

| Cat | 149919 |

| Dog | 1, 338489 |

| Hamster | 1, 19890 |

| Snake | 1, 49791 |

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

Calculate the extreme value of a field

Evaluate the extreme values of a field, involving two functions, MAX () and MIN ().

For example, in the pet table, you want to know the earliest date of birth of an animal. because the earliest date is the minimum, you can use the MIN () function:

Mysql> select min (birth) FROM pet;

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

| MIN (birth) |

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

| 1989-05-13 |

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

However, you only know the date, but you still cannot know which pet it is. you may want to do this:

SELECT name, MIN (birth) FROM pet;

However, this is an incorrect SQL statement, because the set function cannot be used together with non-group columns, and the name column is not grouped here. Therefore, you cannot get the value of the name column and the extreme value of birth at the same time.

The MIN () function can also be used with the group by clause. for example, find the earliest date of birth in each pet:

Mysql> SELECT species, MIN (birth) FROM pet group by species;

The following are satisfactory results:

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

| Species | MIN (birth) |

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

| Bird | 1997-12-09 |

| Cat | 1992-02-04 |

| Dog | 1989-05-13 |

| Hamster |

| Snake | 1996-04-29 |

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

If you want to know that the most recent date of birth is the maximum value of the date, you can use the MAX () function, as shown in the following example:

Mysql> SELECT species, MAX (birth) FROM pet group by species;

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

| Species | MAX (birth) |

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

| Bird | 1998-09-11 |

| Cat | 1994-03-17 |

| Dog | 1990-08-31 |

| Hamster |

| Snake | 1996-04-29 |

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

Summary

This section describes the usage of some typical set functions, including Count, average, extreme value, and sum, which are very common functions in SQL.

These functions are called set functions because they are applied to multiple records. Therefore, the most common usage of set functions is to work with the group by clause, the most important thing is that collection functions cannot be used together with ungrouped columns.

BitsCN.com