8. Sorting and grouping of table data

When we use SQL statements to perform a query operation, we may find that the queried data results are sorted unordered. To better observe the query results in a data table, developers or users often need to sort the queried data, which requires the order by clause. In the actual application of the database, you sometimes need to perform statistics and grouping operations on the queried data. Therefore, you need to understand the Aggregate functions of SQL statements and the use of GROUP BY clauses. In some cases, developers or users also want to make further statistics on the results After grouping. in SQL statements, a keyword such as ROLLUP is provided for statistics on data. Finally, we will introduce how to limit the number of rows in the result set in the primary database.

1. Sort data records using the order by clause

Specify a column in the table for sorting
The order by clause can be used to perform ascending or descending operations on the specified columns in the query results, depending on the keyword after the order by clause. If the keyword after the order by clause is ASC, then, the query results are executed in ascending ORDER. If the keyword after the order by clause is DESC, the query results are executed in descending ORDER. The syntax rules are as follows:
Order by column name 1 [ASC | DESC]
Column name 1 indicates that the column needs to be sorted. The keywords ASC and DESC are optional. If the order by clause is not followed by asc or DESC, the ascending operation is performed BY default.

 dept   salary 

Sequence Number of the specified column in the table
When the order by clause is used for sorting, you can use the column name to sort the specified column or the sequence number of the column in the selected list.

Sort non-selected columns in the SELECT statement
The order by clause can also sort the selected columns that are not displayed in the SELECT statement.

 dept   salary

Specify multiple columns in the table for sorting
In addition to specifying a single column for sorting, the order by clause can also specify multiple columns in a data table for sorting. If you want to specify multiple columns in the data table for sorting, you must use commas to separate the specified columns. The syntax rules are as follows:
Order by column name 1 [ASC | DESC], column name 2 [ASC | DESC]
Column names 1 and 2 indicate that the specified data column needs to be sorted. Use commas (,) to separate column names 1 and 2. The keywords ASC and DESC are optional. If the order by clause is not followed by asc or DESC, the ascending operation is performed BY default. First, sort BY the first column specified in order by. Then, sort BY the ascending or descending ORDER of the second column specified in the order by clause.

  salary ,dept 

2. Common Aggregate functions

Aggregate functions are also called grouping functions or statistical functions. They are mainly used for statistical calculation of a set of data, such as sum and average. Commonly Used Aggregate functions includeCOUNT, MAX, MIN, SUM, and AVGFive.
You can use the DISTINCT keyword in the COUNT, SUM, and AVG functions to remove repeated items in the specified column. After the DISTINCT keyword is used, only the values of different rows are counted.
The columns or expressions in the MAX and MIN functions can be numeric, numeric, or date values. If the columns or expressions in the MAX and MIN functions are sorted in the sequence from A to Z. If the first letter is the same, the size of the second letter in the string is compared, and so on. Chinese characters are sorted by the full spelling of Chinese pinyin.

 (salary), T_teacher

The expressions in SUM and AVG functions can only be numeric values.
Except COUNT (*), several other functions ignore NULL values (NULL rows) in the Expression During computation ).
The COUNT function is used to calculate the total number of rows in a data table. The SUM function is used to calculate the SUM of the attribute values of a column in the data table.

 (salary),(salary), T_teacher

Aggregate functions can only appear in SELECT statements, group by clauses, and HAVING clauses, but not in WHERE clauses.

3. Use the group by clause to GROUP data in the table

Single-Column grouping
When you use the group by clause to group a column in a data table, a statistical result is calculated for different values in the column of the specified GROUP. The syntax format is as follows:
Group by column name 1
Column name 1 indicates that you need to group the column.

 dept,  dept

Select must use Group By when both data columns and Aggregate functions are included.
Multi-column grouping
When you use the group by clause to GROUP multiple columns in a data table, a statistical result is calculated for different values of multiple columns in the specified GROUP. The syntax format is as follows:
Group by column name 1, column name 2...
Column names 1 and 2 indicate that you need to group the specified columns. Use commas (,) to separate column names 1 and 2.
Use HAVING subsentences to limit query results after grouping
If you want to restrict query conditions for grouping results, you need to use the HAVING clause. The HAVING clause is used to limit the query results after grouping. Therefore, this clause must be placed behind the group by clause. The syntax format is as follows:
Group by column name 1 HAVING condition expression
Column name 1 indicates that you need to group the column. The conditional expression after the HAVING clause is used to filter the grouped results. Aggregate functions are often used in HAVING clauses to filter grouped results.

 dept,profession,  (salary)

Note: HAVING is available under group by, and HAVING is used for groups. WHERE is for SELECT (for tables or views), and WHERE is used before distribution.

 profession, age  (salary)

Sort group results
In many cases, After grouping data in a data table, you also want to sort the grouping results. If you want to sort the grouping results using the group by clause, you need to use the order by clause.

 dept,profession,   (salary) 

Sort the group by the highest salary in each GROUP after group by. The following statement sorts the group by the salary in the first row of each GROUP.

 dept,profession,   salary 

Processing NULL values in the group by clause
When you use the group by clause to group a specified column, the specified column may contain NULL values. The group by clause groups all NULL values in the column.
If you want to obtain the row corresponding to the maximum wage of each group (instead of the first row of the group), you can use subqueries and other methods.

 (   t_teacher   salary )    salary

4. Use the ROLLUP keyword statistics

In practical application, you may not only need to get the statistical results after grouping, but also want to further calculate the statistical results of grouping, for example, by the instructor information table (T_teacher) the colleges and teachers' titles in China are grouped to get the teachers' salaries After grouping, and we also hope to make a periodic statistics on the salaries of teachers in each department, we hope to get the sum (equivalent to subtotal) of the salaries of teachers with different titles in each school and the sum (equivalent to the total) of the salaries of teachers with different titles in all schools ). In this case, you cannot use the group by clause only. In this case, you need to use the ROLLUP keyword.
The ROLLUP keyword must be placed behind the group by keyword. The ROLLUP keyword is used slightly differently in different databases.
A. Use with rollup in MySQL and Microsoft SQL Server databases. The syntax format is as follows:

Column name 1 ROLLUP

The column name 1 indicates that the column is to be grouped, And the with rollup keyword indicates that the grouping results are to be counted. Of course, you can also group multiple columns and count the grouping results. The syntax format is as follows:
Group by column name 1, column name 2 WITH ROLLUP
B. in Oracle databases, the ROLLUP keyword must be followed BY the GROUP BY keyword, and then write the fields to be grouped. The syntax format is as follows:
Group by rollup (column name 1, column name 2 ...)

5. Limit the number of rows in the result set

Sometimes, developers or users do not want to display all the data in the data column of the query results, but only want to display several rows, especially in paging operations. For example, a data table finally queries 100 records, and developers or users only care about the values of the first 10 records, therefore, you need to limit the number of data records in the query results. The methods for limiting the number of results set rows in different databases are also different.
A. You can use the LIMIT keyword to LIMIT the number of rows in the result set in the MySQL database. It can be used to LIMIT the number of queried data results. By using the LIMIT keyword, developers or users can get the desired result. If you want to use LIMIT to LIMIT the number of rows in the result set, you can use the following syntax format.
LIMIT n
Here, LIMIT is the keyword, and number n indicates to LIMIT the number of rows in the result set.

 LIMIT ,

B. The Oracle database does not support the LIMIT keyword similar to the LIMIT keyword in MySQL to LIMIT the number of rows in the result set. However, the ROWNUM keyword can be used in the Oracle database to LIMIT the number of rows in the result set. The syntax format is as follows:
Where rownum <n
The ROWNUM keyword indicates the serial number of the result that meets the condition. Its starting value is always from 1. Number n indicates the number of rows in the result set to be restricted. Of course, in addition to using (<) less than, the comparison operator can also use (<=) less than or equal.
C. Use the LIMIT keyword and ROWNUM In the MySQL database and Oracle database to LIMIT the number of result set rows. Use the TOP keyword in the Microsoft SQL Server database. The syntax format is as follows:
Select top n [PRECENT] column name 1, column name 2...
FROM table name
...
TOP is the keyword that limits the number of rows in the result set. number n indicates the number of rows in the result set. the PRECENT keyword indicates the number n % before the returned result set. It is optional.