MySql Group By implements grouping of multiple fields

In daily development tasks, we often use MYSQL's GROUP BY grouping to obtain statistical data based on the grouping 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, and apply the following SQL:

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

The following results are obtained:

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

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

The reasons for this result are:

GROUP BY X means putting all records with the same X field value into one group.

What about GROUP BY X, Y?

GROUP BY X, Y means putting all records with the same X and Y field values ​​into one group.

Next, we need to count how many people choose each subject 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 the 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 is:

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

From the records in the table, we can see that the grouping result is correct. There are 3 students who chose ITB001 in the first semester, 2 students who chose ITB001 in the second semester,

There were also two students who 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.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 how much money each user spent on each product, execute the following SQL

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

The results obtained are as follows:

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

Summarize:

When using GROUP BY in MYSQL to group data in a table,

GROUP BY X means putting all records with the same X field value into one group.

GROUP BY X, Y means putting all records with the same X and Y field values ​​into one group.

The above is the MySql Group By method for grouping multiple fields that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!