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!