Group BY and having usage parsing in SQL

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.