How to use Group By to group multiple fields in MySql

This article mainly introduces the implementation method of MySql Group By to group multiple fields. Friends in need can refer to

Related mysql video tutorial recommendations: " mysql tutorial》

In daily development tasks, we often use MYSQL's GROUP BY grouping to obtain statistical data based on group fields in the data table. For example, there is a student course selection table with the following structure:

Table: Subject_Selection
Subject  Semester  Attendee
---------------------------------
ITB001  1     John
ITB001  1     Bob
ITB001  1     Mickey
ITB001  2     Jenny
ITB001  2     James
MKB114  1     John
MKB114  1     Erica

We want to count how many students have signed up for each course. Apply the following SQL:

SELECT Subject, Count(*)
FROM Subject_Selection
GROUP BY Subject

to get the following results:

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

Because the table records that 5 students chose ITB001 and 2 students chose MKB114.

The reason for this result is:

GROUP BY X means to put all records with the same X field value into a group.

What about GROUP BY X, Y?

GROUP BY X, Y means to put all records with the same X field value and Y field value into a group.

We will then ask to count how many people choose each subject in each semester, and apply the following SQL:

SELECT Subject, Semester, Count(*)
FROM Subject_Selection
GROUP BY Subject, Semester

The above SQL means to group the data in the Subject_Selection table. Put records with the same Subject and Semester field values ​​into the same group, and then apply aggregate functions (COUNT, SUM, AVG, etc.) to the data in each group.

The result obtained is:

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

From the records in the table, we can see that this grouping result is correct. 3 students chose ITB001 in the first semester, and 2 students chose ITB001 in the first semester. IB001 was chosen in the second semester, and two students chose MKB114 in the first semester. No one chose MKB114 in the second semester.

Another example is an order table that records all paid orders

Table: Order

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

Now we want to count each user’s purchase of each product How much money was spent in total? The results obtained by executing the following SQL

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

are as follows:

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

Summary:In MYSQL When using GROUP BY to group the data in the table,

GROUP BY X means to put all records with the same X field value into a group,

GROUP BY X, Y This means putting all records with the same X field value and Y field value into a group.

The above is the detailed content of How to use Group By to group multiple fields in MySql. For more information, please follow other related articles on the PHP Chinese website!