SQL where, group by, and having usage parsing, sqlhaving

SQL where, group by, and having usage parsing, sqlhaving

-- SQL where, group by, and having usage parsing -- if you want to use group by, you generally use "every word". For example, there is a table like this: how many people in each department need to use grouping technology select distinct mentid as 'department name', COUNT (*) as 'number' from BasicDepartment group by partition mentid -- this is to use the group by + field for grouping, here, we can understand that we have grouped the dataset according to the department name ID -- regionmentid; then we can calculate the statistical data of each group separately; -- if you do not need count (*) the following syntax is used: select distinct mentid, DepartmentName from BasicDepartment group by distinct mentid -- an error will occur -- select columns in the list' BasicDepartment. DepartmentName 'is invalid because the column is not included in the aggregate function or group by clause. This is what we need to pay attention to. If these fields in the returned set are included after the Group By statement, they are used as the basis for grouping; either it will be included in the aggregate function as the basis for grouping; -- detailed explanation of the error: Let's look at the execution process of group by, first execute the select Operation to return an assembly, -- then perform the grouping operation. At this time, the group will be performed based on the field after group by, and the same field will be referred to 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, therefore, this error occurs. Currently, only one record is allowed in a group, and one data grid cannot contain multiple values, -- so here we need to convert these multi-value columns into single values through some processing, and then place them in the corresponding -- Data lattice. Then, the aggregate function is used to complete this step. This is why these functions are called aggregate functions -- group by all Syntax Parsing: -- if the ALL keyword is used, the query results will include 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. Select distinct mentid, DepartmentName as 'department name', COUNT (*) as 'COUNT' from BasicDepartment group by all distinct mentid, departmentName ========================================== ========================================================== =========================================-- group by and having explanations: you must understand a special function in SQL: Aggregate functions, such as SUM, COUNT, MAX, and AVG. The fundamental difference between these functions and other functions is that they generally work on multiple records. Having is the filtering Condition After grouping (group by). After grouping, you can filter the where clause in the Data group, but not in the having clause, therefore, HAVING is added to the set function to test whether the query results meet the conditions. That is, the having clause can be used to limit the having clause of the aggregate function, rather than every element in the having clause of the row must also appear in the select list. Some database exceptions. For example, if oracle contains both the where clause, group by clause, having clause, and clustering function, 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 the non-conforming group ex: displays the total population and total area of each region. only the regions with an area greater than 1000000 are displayed. SELECT region, SUM (population), SUM (area) FROM bbc group by region having sum (area)> 1000000 here, we cannot use where to filter over 1000000 of the regions, because such a record does not exist in the table. (Clustering functions cannot be used in where clauses) on the contrary, HAVING clauses allow us to filter the data of each group after grouping. ex: create TABLE Table1 (ID int identity () primary key not null, classid int, sex varchar (10), age int,) -- add and test multiple pieces of data Insert into Table1 values (1, 'male', 20) Insert into Table1 values (2, 'femal', 22) Insert into Table1 values (3, 'male', 23) Insert into Table1 values (4, 'male', 22) Insert into Table1 values (1, 'male', 24 ).......... select COUNT (Age) as '> 20-year-olds', classid from Table1 where sex = 'male' group by classid having COUNT (age)> 2. The example below is good. SQL> select * from SC; sno pno grade ---------- ----- ---------- 1 YW 95 1 SX 98 1 YY 90 2 YW 89 2 SX 91 2 YY 92 3 YW 85 3 SX 88 3 YY 96 4 YW 95 4 SX 89 4 YY 88 this table describes the scores of four students corresponding to each subject, SNO (student ID), PNO (Course name), and GRADE (score ). 1. Show the course name and score of more than 90 students // This is a simple query and does not use grouping query SQL> select sno, pno, grade from SC where grade> = 90; sno pno grade ---------- ----- ---------- 1 YW 95 1 SX 98 1 YY 90 2 SX 91 2 YY 92 3 YY 96 4 YW 95 2. Each student has a score above 90. how many doors -- group display, and count SQL> select sno, COUNT (*) from SC where grade> = 90 group by sno; SNO count (*) according to the where Condition (*) ---------- ---------- 1 3 2 2 4 1 3 3 3. We didn't use the having statement here. If we want to select three good students, we must have at least two courses in If the score is 90 or higher, you can list the number of qualified students and the number of courses with a score of 90 or higher. // Grouping and displaying, and counting based on the where condition, grouping SQL> select sno, count (*) by having clause (*) from SC where grade> = 90 group by sno having count (*)> = 2; SNO COUNT (*) -------- ---------- 1 3 2 2 this result is what we want, it lists student numbers that are qualified to select three good students. After comparison with the previous example, it is found that this is a subquery conducted after the group. 4. for advanced students selected by the school, all students whose average score is greater than 90 are required to be eligible and the Chinese class must be over 95. List qualified students // actually, this query first extracts the student numbers with a language score greater than 95, and then calculates the average value. After grouping and displaying, it selects SQL statements with an average score greater than 90 according to having statements> select sno, avg (grade) from SC where sno in (select sno from SC WHERE GRADE> = 95 AND PNO = 'yw') group by sno having avg (grade)> = 90; SNO AVG (GRADE) ---------- 1 94.3333333 4 90.6666667 5. Comparison and subquery SQL> select sno, avg (grade) from SC group by sno having avg (grade)> (select avg (grade) from SC where sno = 3 );