MySQL group query for data Tables (group by)

MySQL group query for data Tables (group by)

The GROUP BY keyword can group query results by a field or multiple fields. The values in the field are equal to one group. The basic syntax format is as follows:

GROUP by property name [having conditional expression] [with ROLLUP]

• Attribute name: Refers to grouping by the value of the field.
• Having conditional expression: used to restrict the display after grouping, the result of matching the conditional expression will be displayed.
• With ROLLUP: A record will be added at the end of all records. The addition of this record is the sum of all records above.

The GROUP by keyword can be used with the Group_concat () function. The Group_concat () function displays the field values specified in each group.

Also, the GROUP by keyword is typically used with aggregate functions. The aggregate functions include the count () function, the SUM () function, the AVG () function, the MAX () function, the min () function, and so on.

• Count () function: The number of bars used to count records.
• SUM () function: the sum of the values used to calculate the field.
• AVG () function: The average of the values used to calculate the field.
• Max () function: The maximum value for the query field.
• Min () function: The minimum value for the query field.

If group by does not work with the above function, then the query result is the grouping of the field values. The same record in the field is a group, but only the first record of the group is displayed.

Use GROUP BY keyword separately

If the group by keyword is used alone, the query results display only one record of the grouping.

Example: Group the Sex field of the employee table and compare the query results to the results before grouping. The procedure is as follows:

1. First execute the SELECT statement without the group by keyword. As shown in the following:

The results of the code execution in the table show the original records in the employee tables.

2. Execute the SELECT statement with the GROUP by keyword. The code is as follows:

SELECT * from the employee GROUP by sex;

View the action effect of executing a SELECT statement with the group by keyword in the DOS prompt window. As shown in the following:

The results in code execution show only two records. The values for the sex fields of these two records are "female" and "male" respectively.

The query results are compared, and the group by keyword displays only one record for each group. This means that when the group by keyword is used alone, only one record of each grouping can be queried, which makes little sense. Therefore, the group by keyword is generally used when using aggregate functions.

The GROUP by keyword is used with the Group_concat () function

When the group by keyword is used with the Group_concat () function, the field values specified in each grouping are displayed.

Example: Group the employee table by the sex field. Use the Group_concat () function to display the value of each grouped name field.

The code for the SELECT statement is as follows:

SELECT Sex,group_concat (name) from the employee GROUP by sex;

Check the execution of your code in the DOS prompt window. As shown in the following:

The results of the code execution show that the query results are divided into two groups. A record with a value of "female" in the Sex field is a group of records with a value of "male". The names of everyone in each group were queried.

This example shows that using the Group_concat () function can be a good representation of the grouping situation.

The GROUP by keyword is used with the collection function

When the group by keyword is used with a collection function, the aggregate record, maximum value, minimum value, and so on, can be calculated from the grouping function.

Example: Group the Sex field of an employee table into a query. The Sex field takes the same value as a group. Each group is then evaluated using the aggregate function count () function to find out the number of records for each group.

The code for the SELECT statement is as follows:

SELECT sex,count (Sex) from the employee GROUP by sex;

View the action effect of the group by keyword with the collection function in the DOS prompt window. As shown in the following:

The results of the code execution show that the query results are grouped by the value of the sex field. A record with a value of "female" is a group, and a record with a value of "male" is a group. COUNT (Sex) calculates the number of records in different groups of the sex field. There were 2 records in the first group and 3 records in the second group.

Tips

Typically, the group by keyword is used with the collection function to group records by using the group by keyword, and each group is evaluated using aggregate functions. The GROUP BY keyword and aggregate functions are often required for statistical use.

The GROUP by keyword is used with the have

When you use the group BY keyword, you can limit the result of the output if you add a have conditional expression. Only results that match the conditional expression will be displayed.

Example: Group the Sex field of an employee table into a query. A grouping with a record number greater than or equal to 3 is then displayed.

The code for the SELECT statement is as follows:

SELECT sex,count (Sex) from the employee GROUP by sex have COUNT (sex) >=3;

In the DOS prompt window, see the effect of the group by keyword used with the. As shown in the following:

The results in code execution only show the case of records with a value of "male". Because, the number of records for this grouping is 3, just in accordance with the condition of having COUNT (sex) >=3.

This example shows that having conditional expressions can limit the display of query results.

Tips

Both the having conditional expression and the WHERE conditional expression are used to limit the display. However, the two work in different places.

• A WHERE condition expression: action on a table or view, which is a query condition for tables and views.
• Having conditional expression: a record that acts on a group and is used to select a group that matches a condition.

Group by multiple fields

In MySQL, you can also group by multiple fields. For example, the employee table is grouped according to the d_id field and the Sex field. In the grouping process, the records that are equal to the d_id value are grouped by the sex field by grouping the d_id field first and encountering the value of the d_id field.

Example: Group the employee table according to the d_id field and the Sex field.

The code for the SELECT statement is as follows:

SELECT * from the employee GROUP by D_id,sex;

View the effect of grouping by multiple fields in the DOS prompt window. As shown in the following:

The results of the code execution show that the records are grouped by the d_id field first, because the values for the d_id with two records are 1001 and 1004, so the 4 records are again grouped by the value of the sex field.

The GROUP by keyword is used with the WITH rollup

When using with rollup, a record is added at the end of all records. This record is the sum of all the records above.

Example: Group the Sex field of an employee table into a query. Use the count () function to calculate the number of records per group, plus the with ROLLUP.

The code for the SELECT statement is as follows:

SELECT sex,count (Sex) from the employee GROUP by sex with ROLLUP;

View the action effect of the group by keyword with the WITH rollup in the DOS prompt window. As shown in the following:

The results of the code execution show that the number of records for each grouping is calculated, and a new record is added at the end of the record. The value of the Count (sex) column of the record is exactly the sum of the values grouped above.

Example: Group the Sex field of an employee table into a query. Use the Group_concat () function to view the value of the Name field for each group, plus the with ROLLUP.

The code for the SELECT statement is as follows:

SELECT Sex,group_concat (name) from the employee GROUP by sex with ROLLUP;

View the operation effect of the SELECT statement with the rollup parameter in the DOS prompt window. As shown in the following:

The results from the code execution show that Group_concat (name) displays the value of the Name field for each grouping. At the same time, the value of the Group_concat (name) column of the last record is exactly the sum of the values grouped by name above.

MySQL group query for data Tables (group by)