In-depth analysis of SQL group by and having usage, sqlgroup

In-depth analysis of SQL group by and having usage, sqlgroup

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, you generally use the word "per". For example, you 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 distinct 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.

Summary

The above section describes the group by and having usage in SQL. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!