MySQL advanced features of the aggregate function

So far, you've only learned how to take one or more records from a table based on specific conditions. However, if you want to make statistics on the records in a table. For example, if you want to count the poll results of a poll stored in the table. Or you want to know how much time a visitor spends on your site on average. To count any type of data in a table, you need to use aggregate functions. You can count the number, average, minimum, maximum, or sum of records. When you use an aggregate function, it returns only a number that represents one of these statistical values.

The biggest feature of these functions is that they are often used in conjunction with the group BY statement, and it is important to note that aggregate functions cannot be mixed with columns that are not grouped.

Row Count

Calculates the number of rows returned by a query statement

Calculate the value of the function count (*) directly, for example, to 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 species appears more than once, the species will be computed multiple times. If you want to know how many pets there are for a particular value, you can use the WHERE clause as shown in the following example:

Mysql> SELECT COUNT (species) from pet WHERE species= ' cat ';

Notice the result of this statement:

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

| COUNT (species) |

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

| 2 |

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

This example returns the number of authors who are of type ' cat '. If the name appears in the table pet two times, the return value of the secondary 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 kinds of pets there are. You can get that number by using the keyword DISTINCT. As shown in the following example:

Mysql> SELECT COUNT (DISTINCT species) from pet;

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

| COUNT (DISTINCT species) |

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

| 5 |

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

If the kind of ' cat ' appears more than once, it will only be computed once. The keyword distinct determines that only different values are counted.

Typically, when you use count (), the null value in the field is ignored.

In addition, the COUNT () function is usually used in conjunction with the GROUP BY clause, for example, to return the number of each pet:

Mysql> SELECT Species,count (*) from the pet GROUP by species;

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

| Species | COUNT (*) |

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

| Bird | 2 |

| Cat | 2 |

| Dog | 3 |

| Hamster | 1 |

| Snake | 1 |

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

Calculate the average of a field

You need to calculate the average of these values. Using the function avg (), you can return the average value of all the values in a field.

If you have a more complicated poll of your site. Visitors can vote between 1 and 10 to show how much they like your site. You keep the voting results in the Int field named vote. To calculate the average of your user votes, you need to use the function avg ():

SELECT AVG (vote) from opinion

The return value of this SELECT statement represents the average user's preference for your site. The function avg () can only be used for numeric fields. This function also ignores null values when calculating averages.

To give a practical example, for example, to calculate the average age of each animal in a pet table, use the AVG () function and the GROUP BY clause:

Mysql> SELECT Species,avg (curdate ()-birth) from the pet GROUP by species;

The result returned is:

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

| Species | AVG (Curdate ()-birth) |

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

| Bird | 34160 |

| Cat | 74959.5 |

| Dog | 112829.66666667 |

| Hamster | 19890 |

| Snake | 49791 |

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

Calculates the value of a field and

Let's say your site is being used to sell a product and it has been running for two months, and it's time to calculate how much money to make. Suppose a table named orders is used to record order information for all visitors. To calculate the sum of all order quantities, you can use the function sum ():

SELECT SUM (purchase_amount) from Orders

The return value of the function sum () represents the sum of all the values in the field Purchase_amount. The data type of the field Purchase_amount may be of type decimal, but you can also use the function sum () for other numeric fields.

With a less-than-appropriate example, we calculate the total age of the same pet in the pet table:

Mysql> SELECT species,sum (curdate ()-birth) from the pet GROUP by species;

You can look at the results, in contrast to the previous example:

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

| Species | SUM (Curdate ()-birth) |

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

| Bird | 68320 |

| Cat | 149919 |

| Dog | 338489 |

| Hamster | 19890 |

| Snake | 49791 |

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

Calculate the extremum of a field value

Find the extreme value of the field, involving two functions Max () and Min ().

For example, or the pet table, you want to know the earliest animal birth date, because the date is the earliest is the smallest, so you can use the min () function:

mysql> SELECT MIN (birth) from pet;

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

| MIN (birth) |

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

| 1989-05-13 |

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

But, you only know the date, still can't know which pet, you may think of doing this:

SELECT name,min (birth) from pet;

However, this is a bad SQL statement because the aggregate function cannot be mixed with a column that is not grouped, where the name column is not grouped. So, you can't get both the value of the Name column and the birth extreme.

The MIN () function can also be used in conjunction with the GROUP BY clause, for example, to find the earliest date of birth for 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 | 1993-02-04 |

| Dog | 1989-05-13 |

| Hamster | 1999-03-30 |

| Snake | 1996-04-29 |

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

On the other hand, if you want to know the most recent birth date, which 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 | 1999-03-30 |

| Snake | 1996-04-29 |

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

Summarize

In this section, we introduce some typical usage of aggregate functions, including count, mean, extremum, and sum, which are very commonly used in SQL languages.

These functions are called aggregate functions because they are applied to more than one record, so the most common use of aggregate functions is to work with the GROUP BY clause, and most importantly, aggregate functions cannot be mixed with columns that are not grouped.

Note : For more wonderful tutorials, please pay attention to the triple graphics tutorial channel,