mysql group by grouping 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. Three students chose ITB001 in the first semester, two students chose ITB001 in the second semester, and two students chose MKB114 in the first semester. No one chose MKB114 in the second semester.

The above article about mysql group by to group multiple fields is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.