grouping data for SQL Learning Group by

Summary: "GROUP by" is understood literally by grouping data according to the rules specified after "by" (grouping is the partitioning of a dataset into a number of sub-datasets according to the rules specified by "by"), and then data processing on the child datasets.

1, below an example to understand the "Group by" role and function, the code is as follows:

Select *  from Course

This is a course schedule, now there is a reporting program that requires the number of each teacher, and the total number of courses taught by each teacher, here is the solution code:

Select tno,COUNTas fromgroup by TNO

This is the personal understanding, the first group after the group by group, the following data collection contains all the rows of the teacher ID t001 data, this data collection we can use the aggregation function to get the information we want, but we can't get the detailed column information! The reason we can see through the structure can be seen!

OK, complete the demand through GROUP by!

The above select specifies two columns, TNO contains the teacher's number, courses is the calculated field (established with the count () function), and the GROUP BY clause instructs the DBMS to sort and group the data by TNO. This computes the courses once for each TNO instead of the entire table (that is, the DBMS does a count () operation on the individual subset of data (after sorting and grouping by TNO), rather than the whole data set.

2. Here are some important rules you need to know to use the GROUP BY clause

(1) The GROUP BY clause can contain any number of columns, so the grouping can be nested for a more granular grouping.

(2) Each column listed in the Group by clause must be a retrieval column (or a valid expression, note that it cannot be a clustered function). If retrieving a column (or an expression) is used in select, the same expression is used in the GROUP BY clause and the alias cannot be used.

(3) Most SQL does not allow group by with variable-length data types (for example, text, type).

(4) In addition to the aggregation calculation statement, each column in the SELECT statement must be given in group by.

(5) If the grouping column contains rows with null values, then NULL is returned as a grouping, and if there are multiple rows of NULL in the column, they will be returned as a group.

(6) Group by must appear after the WHERE clause before the Order by clause.

(7) If grouping is nested in the GROUP BY clause, the data is summarized on the last specified grouping. In other words, when you create a group, all the columns you specify are evaluated together (you cannot retrieve data from individual columns).

3, GROUP by all+ multiple fields, group by+ multiple fields

In SQL Server, group by all+ multiple fields and group by+ multiple fields are the same in effect, and are grouped by multiple fields! The following code:

Select *  from Course

This is a course schedule, and now there is a report program that needs to show how many classes each teacher teaches (the same course), and the following is the solution code:

Select tno,cname,COUNTfromgroupby allorderby TNO

This is a personal understanding, which is the first set after group by grouping, and the following data collection contains all the row data (teacher ID is t001 and the course name is Oracle) , This data collection we can use the aggregate function to get the information we want, but we can't get the detailed column information! The reason we can see through the structure can be seen!

OK, to solve the demand, through the above results diagram, we can see that three teachers taught the course basically only teach a class, in addition to the t003 Teacher's SQL SERVER 2005 taught two classes, of course, our actual business, and will not be such a table, my side instructions in order to demonstrate the group by+ Multiple fields to complete the function, only to force the construction of this demand!

grouping data for SQL Learning Group by