GROUP query group by usage example

Use group by in SQL to GROUP data of SELECT results. Before using GROUP BY, you need to know some important rules.

1. The group by clause can contain any number of columns. That is to say, the group can be further grouped to provide more detailed control for the data group.
2. If multiple groups are specified in the group by clause, the data is summarized in the last GROUP.
3. Each column listed in the group by clause must be a search column or a valid expression (but not a clustering function ). If an expression is used in 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 the group column contains a NULL value, NULL is returned as a group. If there are multiple rows of NULL values, they are divided into one group.
6. The group by clause must be placed after the WHERE clause and before the order by clause.

Filter groups

Too many HAVING clauses are used for grouping. The HAVING clause supports all WHERE operations. The difference between HAVING and WHERE is that WHERE is used to filter rows, while HAVING is used to filter groups.

Another way to understand the difference between WHERE and HAVING is that WHERE is filtered before grouping, while HAVING is filtered by each group after grouping.

Grouping and sorting

The order by clause should also be used when the group by clause is used. This is the only way to ensure correct data sorting.

SQL SELECT statement execution sequence:

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

For example

Select candidate name, max (total score) as max total score
From tb_Grade
Where examinee name is not null
Group by candidate name
Having max (total score)> 600
Order by max total score

In the preceding example, the SQL statement execution sequence is as follows:

1. First, execute the FROM clause to assemble data FROM the tb_Grade table.
2. Execute the WHERE clause to filter data that is not NULL in the tb_Grade table.
3. Execute the group by clause to GROUP the tb_Grade table BY the "Student Name" column.
4. Calculate the max () aggregation function and calculate the maximum values in the total score by "total score".
5. Execute the HAVING clause to filter course scores greater than 600.
6. Execute the order by clause to sort the final result BY "Max score.

Example

Comments with data tables
------------------------------------------------
| Id | newsID | comment | theTime |
------------------------------------------------
| 1 | 1 | aaa | 11 |
------------------------------------------------
| 2 | 1 | bbb | 12 |
------------------------------------------------
| 3 | 2 | ccc | 12 |

------------------------------------------------

NewsID is the news ID. Each News Record has multiple comments, and theTime is the comment time.

Now you want to view the latest comment on each News article:

Select * from comments group by newsID obviously does not work

Select * from comments group by newsID order by theTime desc is an out-of-group sorting.

The following two methods can be implemented:

(1)
Selet tt. id, tt. newsID, tt. comment, tt. theTime from (
Select id, newsID, comment, theTime from comments order by theTime desc) as tt group by newsID

(2)
Select id, newsID, comment, theTime from comments as tt group by id, newsID, comment, theTime having
TheTime = (select max (theTime) from comments where newsID = tt. newsID)