Summary of the usage of group by and having in SQL

This article mainly introduces the usage analysis of group by and having in SQL. Friends who need it can refer to it

##1. Analysis of the usage of group by in SQL:

The Group By statement literally means "Group according to (by) certain rules" in English.

Function: Divide a data set into several small areas through certain rules, and then perform data processing on several small areas.

Note: group by means sorting first and then grouping!

Example: If you want to use group by, the word "every" is generally used. For example, there is a requirement now: query how many people there are in each department. We need to use the grouping technology

select DepartmentID as '部门名称',COUNT(*) as '个数'
　　from BasicDepartment
　　group by DepartmentID

This is grouping using the group by + field, in which we can understand that we follow the department name ID

DepartmentID groups the data set; then calculates the statistical data of each group;

2. Group by and having explanation

Premise: You must understand a special function in the SQL language-aggregation function.

　For example: SUM, COUNT, MAX, AVG, etc. The fundamental difference between these functions and other functions is that they generally operate on multiple records.

The WHERE keyword cannot be used when using aggregate functions, so HAVING is added to the aggregate function to test whether the query results meet the conditions.

Having is called the grouping filter condition, which is the condition required for grouping, so it must be used in conjunction with group by.

It should be noted that when the where clause, group by clause, having clause and aggregate function are included at the same time, the execution order is as follows:

1. Execute the where clause to find those that meet the conditions Data;

2. Use the group by clause to group the data;

3. Run the aggregate function on the groups formed by the group by clause to calculate the value of each group;

　4. Finally, use the having clause to remove groups that do not meet the conditions.

Every element in the having clause must also appear in the select list. There are some database exceptions, such as oracle.

Both the having clause and the where clause can be used to set restrictions so that the query results meet certain conditions.

The having clause restricts groups, not rows. The results of aggregate function calculations can be used as conditions. Aggregate functions cannot be used in the where clause, but they can be used in the having clause.

The above is the detailed content of Summary of the usage of group by and having in SQL. For more information, please follow other related articles on the PHP Chinese website!