Explanation of the use of GROUP BY in grouped queries and the SQL execution order

In SQL, GROUP BY is used to group data in the results of SELECT. Before using GROUP BY, you need to know some important rules.

• 1. The GROUP BY clause can contain any number of columns. This means that you can group data within groups, providing more detailed control over data grouping.
• 2. If you specify multiple groups 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 search column or a valid expression (but not an aggregate function). If an expression is used in the SELECT, the same expression must be specified 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 group. If there are multiple rows with NULL values, they will be grouped together.
• 6. The GROUP BY clause must come after the WHERE clause and before the ORDER BY clause.

Filter Groups

Excessive use of the HAVING clause for grouping. The HAVING clause supports all WHERE operations. The difference between HAVING and WHERE is that WHERE filters rows, while HAVING is used to filter groups.

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 you use the GROUP BY clause, you should also use the ORDER BY clause. This is the only way to guarantee that the data is sorted correctly.

The execution order of SQL SELECT statement is:

• 1. The from clause assembles data from different data sources;
• 2. The where clause filters the rows based on the specified conditions;
• 3. The group by clause divides the data into multiple groups;
• 4. Use aggregate functions for calculations;
• 5. Use the having clause to filter groups;
• 6. Calculate all expressions;
• 7. Use order by to sort the result set;
• 8. Select the collection output.

Let me give you an example.

select candidate name, max(total score) as max total score from tb_Grade
where candidate name is not null
group by candidate name having max(total score) > 600
order by max total score

In the above example, the SQL statements are executed in the following order:

• 1. First, execute the FROM clause to assemble the data source data from the tb_Grade table
• 2. Execute the WHERE clause to filter all data in the tb_Grade table that is not NULL
• 3. Execute the GROUP BY clause to group the tb_Grade table by the "Student Name" column
• 4. Calculate the max() aggregate function and find the largest values ​​in the total score according to the "total score"
• 5. Execute the HAVING clause to filter courses with a total score greater than 600 points.
• 6. Execute the ORDER BY clause and sort the final results by "Max Score".

Note: If join and on are used, on will be executed before where, then join will be executed, and then where will be executed.

Attachment:

Aggregate functions in MySQL:

• 1. count() returns the number of rows in a column
• 2. avg() returns the average value 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 null values, count(*) counts all rows, and count(column) ignores rows with null values

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links