What is the usage of group by in mysql

The usage of group by in mysql is to cooperate with the aggregate function and use the grouping information to perform statistics, such as "select name,sum(id) from test group by name,number".

# Recommended: "

mysql video tutorial》

First look at Table 1 below. The table name is test:

Execute the following SQL statement:

SELECT name FROM test GROUP BY name

You should easily know the results of the operation. That's right, it's Table 2 below:

But in order to better understand the application of "group by" multiple columns" and "aggregation function", I suggest that in the process of thinking, use the table In the process from 1 to table 2, add a fictitious intermediate table:
Virtual table 3. Let’s talk about how to think about the execution of the above SQL statement:

1.FROM test: This sentence After execution, the result should be the same as Table 1, which is the original table.

2.FROM test Group BY name: After executing this sentence, we imagine that virtual table 3 is generated, as shown in the figure below, the generation process It is like this: group by name, then find the column of name, and merge the rows with the same name value into one row. For example, if the name value is aa, then the two rows of and are merged. into one row, and all id values ​​and number values ​​are written into one cell, as shown in the figure below

3. Next, execute the Select statement for virtual table 3:

(1) If select * is executed, the returned result should be virtual table 3. However, the contents of some cells in id and number have multiple values, so id and number will return the values ​​in their respective cells. Sort the first value. The id column will return 1, 3, 5

(2) Let’s look at the name column again. Each cell has only one data, so if we select name, there will be no problem. Why Each cell in the name column has only one value, because we use the name column to group by.

(3) So what should we do if the cells in id and number have multiple data? The answer is to use aggregate functions. Aggregate functions are used to input multiple data and output one data. Such as count(id), sum(number), and the input of each aggregate function is each cell with multiple data.

(4) For example, if we execute select name,sum(number) from test group by name, then sum will perform sum operation on each cell of the number column of virtual table 3, for example, on the row with name aa The number column performs the sum operation, that is, 2 3, returns 5, and the final execution result is as follows:

5) How to understand group by multiple fields: such as group by name, number, we can Name and number are regarded as a whole field, and they are grouped as a whole, as shown in the figure below:

6) Next, you can operate with select and aggregate functions. For example, if you execute select name,sum(id) from test group by name,number, the result is as follows:

The above is the detailed content of What is the usage of group by in mysql. For more information, please follow other related articles on the PHP Chinese website!