Analysis of group by and having usage in SQL, grouphaving
I. Analysis of group by usage in SQL:
The Group By statement is interpreted literally as "grouping by certain rules )".
Purpose: use certain rules to divide a dataset into several small areas and then process data for several small areas.
Note: group by is sorted first and then grouped!
For example, if you want to use group by,Each", For example, there is a need to query the number of people in each department. Grouping technology is required
Select distinct mentid as 'department name', COUNT (*) as 'Count'
From BasicDepartment
Group by dimension mentid
The group by + field is used for grouping. We can understand it as the Department name ID.
DepartmentID groups the dataset, and then counts the statistics of each group;
Ii. group by and having
Prerequisite: You must understand a special function in SQL-aggregate function.
For example, SUM, COUNT, MAX, and AVG. The fundamental difference between these functions and other functions is that they generally work on multiple records.
The WHERE keyword cannot be used when a set function is used. Therefore, HAVING is added to the set function to test whether the query results meet the conditions.
Having is called a group filter condition, that is, a condition required by a group. Therefore, it must be used with group.
Note: When the where clause, group by clause, having clause, and clustering function are included at the same time, the execution sequence is as follows:
1. Execute the where clause to search for qualified data;
2. Use the group by clause to group data;
3. Run the aggregate function for the group formed by the group by clause to calculate the values of each group;
4. Use the having clause to remove non-conforming groups.
Each element in the having clause must also appear in the select list. 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 limits groups rather than rows. The aggregate function calculation result can be used as a condition. The aggregate function cannot be used in the where clause, but the having clause can.
References:
Http://www.cnblogs.com/wang-123/archive/2012/01/05/2312676.html