First, the group by usage in SQL is resolved:
The GROUP BY statement is understood in the literal sense of English as "grouping (group) according to certain rules".
Role: A set of rules to divide a dataset into a number of small areas, and then for a number of small areas for data processing.
Note: Group by IS sorted first and then grouped!
For example, if you want to use group by, the word " every " is used, for example, there is now a requirement: Query how many people are in each department. We need to use the technique of grouping.
Select DepartmentID as ' department name ', COUNT (*) as ' number '
From Basicdepartment
GROUP BY DepartmentID
This is done using the group by + field to group, where we can understand as we follow the department's name ID
DepartmentID data sets are grouped, and then the statistics of each group are divided into numbers;
Ii. Group BY and having interpretation
Premise: You must understand a special function in the SQL language--aggregate function.
For example: SUM, COUNT, MAX, AVG, and so on. The fundamental difference between these functions and other functions is that they generally function on more than one record.
The where keyword cannot be used when using a collection function , so the set function is added to the function to test whether the query result matches the condition.
Having is called a grouping filter condition, which is the condition required for grouping , must be associated with group by .
Note: When you include the WHERE clause, the GROUP BY clause, the HAVING clause, and the aggregation function, the order of execution is as follows:
1. Execute the WHERE clause to find the data that meets the criteria;
2. Use GROUP BY clauses to group data;
3. Calculate the value of each group by running the aggregate function of GROUP BY clause;
4. Finally, the HAVING clause is used to remove the group that does not meet the criteria.
Each element in a 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 constraints so that the query results meet certain conditional limits.
a HAVING clause restricts a group, not a row. The result of an aggregate function calculation can be used when the condition is in use, the WHERE clause cannot use the aggregate function, and the HAVING clause can.
Reference article:
Http://www.cnblogs.com/wang-123/archive/2012/01/05/2312676.html
Group BY and having usage parsing in SQL