Analysis of groupby and having usage in SQL

The article details the usage of groupby and having in SQL and some examples. If you need to learn, please refer to this article.

The article details the usage of group by and having in SQL and some examples. If you need to learn, please refer to this article.

First, group
-- The Group By statement is interpreted literally as "grouping by certain rules )".
-- It is used to divide a dataset into several small areas through certain rules, 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 "every word". For example, there is a table like this: the number of people in each department needs to use grouping technology.

The Code is as follows:
Using mentid as 'department name ', COUNT (*) as 'Count' from BasicDepartment group by partition mentid

-- The group by + field is used for grouping, which can be understood as the Department name ID.
-- Grouping mentid groups the dataset and then counts the statistics of each group;
-- Use a syntax similar to the following if you do not use count (*)

The Code is as follows:
Select distinct mentid, DepartmentName from BasicDepartment group by distinct mentid

-- An error will occur.
-- Message 8120, level 16, status 1, 1st rows
-- Selecting the 'basicdepartment. departmentname' column in the list is invalid because it is not included in the aggregate function or group by clause.
-- This is one thing we need to note. If these fields in the returned set are included after the Group By statement,
-- Serves as the basis for grouping; it must be included in aggregate functions.
-- Detailed error Description: Let's take a look at the execution process of group by. First, execute the select Operation to return an assembly,
-- Then execute the group operation. At this time, it will follow the field
-- Grouping, and adding the same field as a column of data. If this field is not followed by group by, it will be divided into a lot of data.
-- But grouping can only divide the same data into two columns, and one column can only be placed in one field, so those that are not grouped
-- The data system does not know where to put the data, so this error occurs.
-- Currently, a group has only one record. A data grid cannot contain multiple values,
-- Here we need to convert these multi-value columns into single values through some processing, and then place them in the corresponding
-- In the data grid, the aggregate function is used to complete this step. That's why these functions are called aggregate functions.

-- Group by all Syntax Parsing:
-- If the ALL keyword is used, the query result includes ALL groups generated BY the group by clause, even if some groups do not have rows that match the search criteria.
-- If the ALL keyword is not found, the SELECT statement containing the group by clause does not display a GROUP with no rows meeting the conditions.

The Code is as follows:
Select partition mentid, DepartmentName as 'department name ', COUNT (*) as 'Count' from BasicDepartment group by all distinct mentid, DepartmentName

-- Group by and having explanation: the premise is that you must understand a special function in the SQL language: aggregate function,
-- Such as 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.

The Code is as follows:
Create TABLE Table1 ( ID int identity (1, 1) primary key not null, Classid int, Sex varchar (10 ), Age int, )

-- Add Test Data

The Code is as follows:

Insert into Table1 values (1, 'male', 20) Insert into Table1 values (2, 'female ', 22) Insert into Table1 values (3, 'male', 23) Insert into Table1 values (4, 'male', 22) Insert into Table1 values (1, 'male', 24) Insert into Table1 values (2, 'female ', 19) Insert into Table1 values (4, 'male', 26) Insert into Table1 values (1, 'male', 24) Insert into Table1 values (1, 'male', 20) Insert into Table1 values (2, 'female ', 22) Insert into Table1 values (3, 'male', 23) Insert into Table1 values (4, 'male', 22) Insert into Table1 values (1, 'male', 24) Insert into Table1 values (2, 'female ', 19

-- For example, query the table to query the number of male persons with the age greater than 20 in each class.

The Code is as follows:
Select COUNT (*) as '> 20-year-olds', classid from Table1 where sex = 'male' group by classid, age having age> 20

-- 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:
-- Execute the where clause to search for qualified data;
-- Use the group by clause to group data. Run the clustering function on the group formed by the group by clause to calculate the values of each group. 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. Some 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. Clustering functions cannot be used in the where clause, while the having clause can