Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use the group by method in the database, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Group By

Concept

Group By sentence, literally understood in English, means "Group according to (by) certain rules".

Function: divide a data set into several small regions through certain rules, and then process the data for several small regions.

Note: group by is sorted first and then grouped!

Grammar

SELECT expression1, expression2,... Expression_n, aggregate_function (aggregate_expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2,... Expression_ n [order BY expression [ASC | DESC]]

Grammatical description

Expression1,expression2,... Expression_n

The expression is not encapsulated in the aggregate function and must be included in the GROUP BY clause at the end of the SQL statement.

Aggregate_function

This is an aggregate function, such as the SUM,COUNT,MIN,MAX or AVG function.

Aggregate_expression

This is the column or expression that will use aggregate_function.

Tables

The table from which you want to retrieve records. At least one table must be listed in the FROM clause.

Where

Optional. These are the conditions that must be met for the record to be selected.

ORDER BY expression

Optional. The expression used to sort the records in the result set. If more than one expression is provided, the values should be separated by commas.

ASC

Optional. ASC sorts the result set in ascending order by expression. This is the default behavior if no modifier is the provider.

DESC

Optional. DESC sorts the result set by expression in descending order.

Common aggregate function

Count () count

Sum () summation

Avg () average

Maximum value of max ()

Minimum min ()

Give an example

Let's take the following two tables as examples:

Student form (Student)

IDStudentNameStudentClassID1 Xiao Ming 12 Xiao Hong 23 Xiao Lan 34 Xiao Lu 25 Xiao Zi 1

Class table (Class)

ClassIDClassName1 Software Class 1, Class 2, Software Class 3, Software Class 3

How many students are there in each class?

SELECT cl.ClassName,COUNT (stu.StudentClassID) AS studentNumFROM student stu LEFT JOIN class cl ON stu.StudentClassID = cl.ClassIDGROUP BY cl.ClassName

Calculation results:

ClassNameStudentName Software Class 1, Class 2, Software Class 2, Software Class 3, Class 1

HAVING

The HAVING statement is often used in conjunction with the GROUP BY statement to filter the recordset returned by the GROUP BY statement.

The existence of the HAVING statement makes up for the deficiency that the WHERE keyword cannot be used in conjunction with the aggregate function.

Popular saying: WHERE filter line, HAVING filter group

Grammar

SELECT expression1, expression2,... Expression_n, aggregate_function (aggregate_expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2,... Expression_n

Grammatical description

HAVING condition this is another condition that applies only to aggregate results to limit the groups that return rows. Only those groups that are evaluated as TRUE are included in the result set.

Give an example

Use the above two data tables

Inquire about classes with more than 2 students?

SELECT cl.ClassName,COUNT (stu.StudentClassID) AS studentNumFROM student stu LEFT JOIN class cl ON stu.StudentClassID = cl.ClassIDGROUP BY cl.ClassNameHAVING COUNT (stu.StudentClassID) > = 2

Calculation results:

ClassNameStudentName Software Class 1, Class 2, Software Class 2

Summary

When group by is used with aggregate function, the function is calculated after grouping.

When group by is used in conjunction with having, the function is to filter after grouping to obtain the return results of packets that meet the conditions.

The difference between having and where: where filter rows, having filter group

After reading the above, do you have any further understanding of how to use the group by method in the database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.