mysql group multiple columns

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, the table structure is as follows:

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, use the following SQL

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

The following results were obtained:

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, and no one chose MKB114 in the second semester.

Summary:

When using GROUP BY to group data in a table in MYSQL,
GROUP BY X means to put all records with the same X field value into one group Here,
GROUP BY X, Y means to put all records with the same X field value and Y field value into a group

The above is the detailed content of mysql group multiple columns. For more information, please follow other related articles on the PHP Chinese website!