Home > Database > Mysql Tutorial > body text

How to use Group By to group multiple fields in MySql

黄舟
Release: 2019-03-02 16:05:21
Original
7033 people have browsed it

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
Copy after login

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
Copy after login

to get the following results:

Subject  Count
------------------------------
ITB001   5
MKB114   2
Copy after login

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
Copy after login

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
Copy after login

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
Copy after login

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
Copy after login

are as follows:

Product  Buyer   SUM
------------------------------
PD001   Todd   36.00
PD001   Lily   24.00
PD002   Todd   40.00
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template