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

This article is about what the relationship between aggregate functions and group by is. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Preface

World: world form

Continent: continent name

Name: name of the country

Population: population

Introduction to aggregate function sum () summation function avg () average function max () maximum function min () minimum function count () introduction to line count function group by

Group up + field name: specifies which field is grouped and aggregated

When used alone, the effect of group weight removal is the same as that of distinct, but the logic is not the same: first, the same field value is divided into a region, and then the same area is taken out for grouping, and the corresponding value is divided into as many groups. Grouping is to weed out the same fields. To put it simply, it breaks the format of the table and generates a new table.

For example, the table above is the result of the partition formed after the execution of group up, dividing the same field values together. The following table is the result of performing the group by grouping, which is de-duplicated based on the results of the partition above.

Explain the relationship between aggregate function and group by

So why would using group by produce such a result? We can use the upper aggregate function to analyze the cause and execute the following SQL code.

Select continent,count (name) from world group by continent

The result is

So let's try to get rid of group by continent and continent and get the following results

After the result is executed, the query count (name) is only the total number of rows in all the name columns, and it is not possible to group each continent (continent) to count the number of all countries (name) in each continent.

This is the function of the aggregate function used in conjunction with group by to help the aggregate function find the grouped table to calculate. In this sentence

Select continent,count (name) from world group by continent

In the SQL statement, the group by is grouped first, the select continent is carried out, and finally the count (name) is carried out, which is based on the grouping after group by.

We can delete the field name of continent to see whether the result is unified, as a confirmation.

Obviously, whether we display the continent or not, the result is the same.

Through this test, we can draw the corresponding conclusion: the table has already been generated when group up is executed, and select just chooses to show it or not to show it, which has no effect on the result. The function of the aggregate function is to calculate in the new tables generated, abandoning the tables that are not grouped.

Points to pay attention to when using group by and aggregate functions

When using the group up clause, select can only use aggregate functions and fields referenced by group up, otherwise it will report an error!

Try to execute the following SQL statement:

Select continent,count (name), population from world group by continent

Why is there an error? because in this SQL statement, group by has been run first, so select cannot appear in fields that are not in group by, and can only be matched based on fields in the table on which the aggregation is based.

Thank you for reading! This is the end of the article on "what is the relationship between aggregate function and group by". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.