Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to use aggregate function and grouping function in Mariadb". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use aggregate function and grouping function in Mariadb".

Aggregate function

When we need to summarize the data in the table instead of using a row of data in the table, we can use the aggregate function provided by Mysql. In Mysql, there are five common aggregate functions:

AVG: take the average COUNT: take the statistical value MAX: take the maximum value MIN: take the minimum SUM: take the sum value

1. Get the sum of the population in the table

MariaDB [world] > SELECT SUM (Population) FROM city;+-+ | SUM (Population) | +-+ | 1429559884 | +-+ 1 row in set (0.01 sec)

two。 Get the total population of China in the data table

MariaDB [world] > SELECT SUM (Population) FROM city WHERE CountryCOde='CHN';+-+ | SUM (Population) | +-+ | 175953614 | +-+ 1 row in set (sec)

3. Find out how many cities there are in China.

MariaDB [world] > SELECT COUNT (ID) FROM city WHERE CountryCode = 'CHN';+-+ | COUNT (id) | +-+ | 363 | +-+ 1 row in set (0.00 sec)

With regard to COUNT, if you want to count how many rows there are in the table, do not use * as a parameter, because ~ affects performance, just choose a column.

4. Inquire about the population of the least populous city in China.

MariaDB [world] > SELECT MIN (Population) FROM city WHERE CountryCode = 'CHN';+-+ | MIN (Population) | +-+ | 89288 | +-+ 1 row in set (sec)

5. Inquire about the population of the most populous city in China

MariaDB [world] > SELECT MAX (Population) FROM city WHERE CountryCode = 'CHN';+-+ | MAX (Population) | +-+ | 9696300 | +-+ 1 row in set (sec)

6. Inquire about the average population of cities in Henan

MariaDB [world] > SELECT AVG (Population) FROM city WHERE District = 'Henan';+-+ | AVG (Population) | +-+ | 383278.3333 | +-+ 1 row in set (sec)

So, we see that aggregate functions are usually used for numerical calculations.

Grouping query

Above, when we use the aggregate function, we group the data of all SELECT. If we want to query the number of cities in all countries, we have to use WHERE to filter CountryCode many times.

MariaDB [world] > SELECT DISTINCT CountryCode FROM city;+-+ | CountryCode | +-+ | ABW | | AFG |. | ZWE | +-+ 232 rows in set (0.00 sec)

We see that there are 232 countries in our data sheet, so? Is it necessary for us to use COUNT (ID) in each of these 232 different countries to count the number of cities in each country? Actually this is not so.

We can use grouping query GROUP BY. What is grouping? A grouping query is a logical grouping of data using a specified column or columns (grouped into a group when the grouping is the same), assuming the following data:

MariaDB [world] > SELECT * FROM city LIMIT 5 +-+ | ID | Name | CountryCode | District | Population | + -+-+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | +-+ 5 rows in set (0.00 sec)

We use GROUP BY CountryCode to specify CountryCode as the grouping basis, so they are divided into the same group and 5 in another group.

Grouping is usually used in conjunction with aggregate functions, which aggregate each individual logical grouping. Each column listed in the GROUP BY clause must be a retrieval column or a valid expression (but not an aggregate function). If you use an expression in SELECT, you must specify the same expression in the GROUP BY clause and cannot use an alias. Except for the aggregate function, each column in the SELECT statement must be given in the GROUP BY clause. If the grouping contains rows with null values, NULL is returned as a grouping. The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.

1. Inquire about the number of cities in each country

MariaDB [world] > SELECT CountryCode,COUNT (ID) FROM city GROUP BY CountryCode +-+-+ | CountryCode | COUNT (ID) | +-+-+ | ABW | 1 | AFG | 4 |.. | ZMB | 7 | | ZWE | 6 | +-+-+ 232 rows in set (0.00 sec)

When the WHERE clause is used in a SELECT statement, the WHERE clause is always filtered before grouping.

two。 Inquire about the number of cities with a population of more than 1000000 in each country

MariaDB [world] > SELECT CountryCode,COUNT (ID) FROM city WHERE Population > = 1000000 GROUPP BY CountryCode +-+-+ | CountryCode | COUNT (ID) | +-+-+ | AFG | 1 | AGO | 1 | ARG | 3 | ARM | 1 | AUS | 4 | ... | YUG | 1 | ZAF | 1 | | ZMB | 1 | ZWE | 1 | +-+-+ 77 rows in set (0.01sec)

So in countries that do not appear in the group, there are no cities with a population of 1000000.

3. Inquire about countries with a total population of more than 1000000

The HAVING clause is used to filter the total value of the grouped data, while the operations supported by HAVING are the same as the WHERE clause.

For example:

MariaDB [world] > SELECT CountryCode,SUM (Population) AS Total_Population FROM city GROUP BY CountryCode HAVING Total_Population > 1000000 +-+-+ | CountryCode | Total_Population | +-+-+ | AFG | 2332100 | | AGO | 2561600 | ARE | 1728336 |. | | ZAF | 15196370 | ZMB | 2473500 | | ZWE | 2730420 | +-+-+ 108 rows in set (0.00 sec) Thank you for your reading. The above is the content of "how to use aggregate function and grouping function in Mariadb". After the study of this article, I believe you have a deeper understanding of how to use aggregate function and grouping function in Mariadb. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.