Shulou(Shulou.com)06/01 Report--

When you use GROUP BY in SQL to group the results of SELECT, you need to know some important rules before you use GROUP BY.

1. The GROUP BY clause can contain any number of columns. In other words, it can be grouped in the group to provide more detailed control for the data packet. 2. If multiple groups are specified in the GROUP BY clause, the data will be aggregated on the last specified group. 3. Each column listed in the GROUP BY clause must be a retrieval column or a valid expression (but not an aggregation function). If you use an expression in SELECT, you must specify the same expression in the GROUP BY clause. Aliases cannot be used. 4. Except for the aggregate calculation statement, each column in the SELECT statement must be given in the GROUP BY clause. 5. If there is a null value in the grouping column, NULL will be returned as a grouping. If there are multiple rows of null values, they are grouped together. 6. The GROUP BY clause must come after the WHERE clause and before ORDER BY.

Filter grouping

Too much HAVING clause is used for grouping. The HAVING clause supports all WHERE operations. HAVING differs from WHERE in that WHERE filters rows, while HAVING is used to filter packets.

Another way to understand the difference between WHERE and HAVING is that WHERE filters before grouping, while HAVING filters on a per-group basis after grouping.

Grouping and sorting

Generally, when using the GROUP BY clause, you should also use the ORDER BY clause. This is the only way to ensure that the data is sorted correctly.

The order in which SQL SELECT statements are executed:

1, the from clause assembles the data from different data sources; 2, the where clause filters the record rows based on the specified conditions; 3, the group by clause divides the data into multiple groups; 4, the aggregate function is used for calculation; 5, the grouping is filtered using the having clause; 6, all expressions are calculated; 7, the result set is sorted using order by; 8, the output of the select collection.

Give me an example.

Select examinee name, max (total score) as max total score from tb_Gradewhere examinee name is not nullgroup by examinee name having max (total score) > 600order by max total score

In the above example, the order in which SQL statements are executed is as follows:

1. First, execute the FROM clause, assemble the data of the data source from the tb_Grade table 2, execute the WHERE clause, filter all the data in the tb_Grade table that are not NULL, execute the GROUP BY clause, group the tb_Grade table according to the "student name" column, calculate the max () aggregate function, calculate some of the largest values in the total score according to the "total score", and execute the HAVING clause. 6. Execute the ORDER BY clause and sort the final results according to the Max score.

Note: if you use connections between join and on, on is executed before where execution, followed by join, followed by where.

Attached:

Aggregate functions in MySQL:

1. Count () returns the number of rows of a column 2, avg () returns the average of a column 3, max () returns the maximum value of a column 4, min () returns the minimum value of a column 5, sum () returns the sum of a column 6, distinct removes duplicate values

Note: avg () ignores rows with the value of null. All rows are counted when count (*), and rows with null are ignored when count (column)

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.