MySql Group By is used to Group multiple fields.

MySql Group By is used to Group multiple fields.

In normal development tasks, we often use mysql group by to obtain statistics based on grouping fields in data tables. For example, there is a student selection table with the following table structure:

Table: Subject_SelectionSubject  Semester  Attendee---------------------------------ITB001  1     JohnITB001  1     BobITB001  1     MickeyITB001  2     JennyITB001  2     JamesMKB114  1     JohnMKB114  1     Erica

We want to count the number of students enrolled in each course and apply the following SQL statement:

SELECT Subject, Count(*)FROM Subject_SelectionGROUP BY Subject

The following result is displayed:

Subject  Count------------------------------ITB001   5MKB114   2

Because the table records that five students choose ITB001 and two students choose MKB114.

The reason for this result is:

Group by x means to put all records with the same X field value in a GROUP.

So what about group by x and Y?

Group by x, Y means to put all records with the same X field value and Y field value into a GROUP.

Next we need to calculate the number of people in each subject per semester and apply the following SQL statement:

SELECT Subject, Semester, Count(*)FROM Subject_SelectionGROUP BY Subject, Semester

The preceding SQL statement groups the data in the Subject_Selection table and puts records with the same Subject and Semester Field Values in the same group, then, the aggregate function (COUNT, SUM, AVG, etc) is applied to the data in each group ).

The result is:

Subject  Semester  Count------------------------------ITB001   1     3ITB001   2     2MKB114   1     2

From the records in the table, we can see that the group results are correct. Three students chose ITB001 in the first semester, and two students chose ITB001 in the second semester,

Two other students chose MKB114 in the first semester, and no one chose MKB114 in the second semester.

For example, there is an order table that records all paid orders.

Table: Order

Product  Buyer    Spending---------------------------------PD001   Todd     12.00PD001   Todd     12.00PD001   Todd     12.00PD001   Lily     12.00PD001   Lily     12.00PD002   Todd     20.00PD002   Todd     20.00

Now we want to calculate the total cost of each user for each item, and execute the following SQL statement:

SELECT Product，Buyer, SUM(Spending)FROM `Order`GROUP BY Product, Buyer

The result is as follows:

Product  Buyer   SUM------------------------------PD001   Todd   36.00PD001   Lily   24.00PD002   Todd   40.00

Summary:

When MYSQL uses group by to GROUP data in a table,

Group by x means to put all records with the same X field value in one GROUP,

Group by x, Y means to put all records with the same X field value and Y field value into a GROUP.

The above section describes how to Group multiple fields in MySql Group By. I hope it will be helpful to you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!