Home >Database >Mysql Tutorial >How to use Group By to group multiple fields in MySql
How to use Group By to group multiple fields in MySql
- 黄舟Original
- 2018-05-25 15:31:147077browse
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!