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 |
+-------------------------+