preface

This chapter mainly introduces the usage of group by in database, which is also a very basic knowledge point when we use database. In addition, it will also involve the use of Join. For the use of Join, you can see my last article: Take you to understand the use of Join in the database. If there is any error, please timely point out ~

The following are all mysql databases

Group By

concept

The Group By statement is used to Group groups By certain rules. Function: Divide a data set into several small areas by certain rules, and then conduct data processing for several small areas.

Group by = group by = group by

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 ]];
Copy the code

Grammar specification

Expression1, expression2… Expression_n expressions are not encapsulated in aggregate functions 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 functions.

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 criteria that must be met for the record to be selected.

The ORDER BY expression is optional. An expression used to sort the records in a result set. If multiple expressions are provided, the values should be comma-separated.

ASC optional. ASC sorts the result set in ascending order by expression. This is the default behavior if no modifiers are providers.

DESC Optional. DESC sorts result sets in descending order by expression.

Common aggregate function

  • The count () count
  • The sum () sum
  • The mean avg ()
  • Max () the maximum value
  • The min () the minimum value

For example,

Let’s take the following two tables as examples:

Student Table

ID StudentName StudentClassID
1 Xiao Ming 1
2 The little red 2
3 Xiao LAN 3
4 Xiao lu 2
5 Small catalpa 1

Class Table

ClassID ClassName
1 The software class
2 The software class two
3 The software class three
  • Calculate how many students are there in each class?

SELECT cl.ClassName,COUNT(stu.StudentClassID) AS studentNum FROM student stu LEFT JOIN class cl ON stu.StudentClassID = Cl.classid GROUP BY cl.className;

Calculation results:

ClassName StudentName
The software class 2
The software class two 2
The software class three 1

HAVING

  • The HAVING statement is usually used in conjunction with the GROUP BY statement to filter the recordset returned BY the GROUP BY statement.
  • The HAVING statement makes up for the fact that the WHERE keyword cannot be used in conjunction with aggregate functions. WHERE to filter rows, HAVING filter groups

grammar

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
Copy the code

Grammar specification

HAVING condition This is another condition that applies only to aggregate results to limit the group of rows returned. Only groups whose conditions are evaluated as TRUE will be included in the result set.

For example,

The above two data tables are used

  • Query a class with more than 2 students?

SELECT cl.ClassName,COUNT(stu.StudentClassID) AS studentNum FROM student stu LEFT JOIN class cl ON stu.StudentClassID = cl.ClassID GROUP BY cl.ClassName HAVING COUNT(stu.StudentClassID)>=2;

Calculation results:

ClassName StudentName
The software class 2
The software class two 2

summary

  • When group by is used together with the aggregate function, the function is to calculate after grouping
  • When group by and HAVING are used together, the function is post-group filtering to obtain the returned results of the groups that meet the conditions.
  • Having differs from WHERE: WHERE to filter rows, having to filter groups

At the end of the article

This chapter mainly introduces the usage of group by in the database, without detailed explanation. I believe that as long as you lay a solid foundation, no matter how complex the query can be solved by the idea of divide and conquer. Welcome to pay attention to the public number: Coder programming to obtain the latest original technical articles and related free learning materials, anytime, anywhere to learn technical knowledge!

Recommended reading

Learn how to use a JOIN in a database

An article to help you understand the difference and connection between processes and threads

A guide to understand the TCP “sliding window” protocol

Reference article:

www.techonthenet.com/sql/having….

www.techonthenet.com/sql/group_b…

www.cnblogs.com/8335IT/p/58…

Github home page directory

Gitee personal home page directory

Welcome to follow and Star~