MySQL group by method for single word grouping sequence and multi-field grouping

I have created a goods table here. Let's take a look at the data in it:

mysql> select * from goods;
+----+------+------+------------+-------------+------------+
| id | s_id | b_id | goods_name | goods_price | goods_desc |
+----+------+------+------------+-------------+------------+
| 1 | 1 | 5 | book | 22.35 | book |
| 2 | 2 | 5 | ball | 32.25 | ball |
| 3 | 3 | 5 | NULL | 3.23 | NULL |
| 4 | 3 | 5 | macbook | 3.23 | book |
| 5 | 3 | 5 | listbook | 2.30 | book |
| 6 | 1 | 1 | nicebook | 9999.00 | nicebook |
| 7 | 2 | 3 | googlebook | 25.30 | book |
+----+------+------+------------+-------------+------------+

1. Group by s_id

mysql> select *,group_concat(goods_name) goods_names,group_concat(goods_desc) goods_descs,group_concat(id) ids,group_concat(goods_price) goods_prices from goods group by s_id;
+----+------+------+------------+-------------+------------+------------------+---------------+-------+----------------+
| id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices |
+----+------+------+------------+-------------+------------+------------------+---------------+-------+----------------+
| 1 | 1 | 5 | book | 22.35 | book | book,nicebook | book,nicebook | 1,6 | 22.35,9999.00 |
| 2 | 2 | 5 | ball | 32.25 | ball | ball,googlebook | ball,book | 2,7 | 32.25,25.30 |
| 3 | 3 | 5 | NULL | 3.23 | NULL | macbook,listbook | book,book | 3,4,5 | 3.23,3.23,2.30 |
+----+------+------+------------+-------------+------------+------------------+---------------+-------+----------------+

The group_concat() function is used here to display detailed information about the group.

The above grouping by a single field is very simple, and all records with the same s_id are grouped together.

2. Group by s_id, goods_desc fields

Analysis: When querying groups here, the data will be grouped according to s_id first, and then the data in each group will be grouped according to goods_desc

mysql> select *,group_concat(goods_name) goods_names,group_concat(goods_desc) goods_descs,group_concat(id) ids,group_concat(goods_price) goods_prices from goods group by s_id,goods_desc;
+----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+
| id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices |
+----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+
| 1 | 1 | 5 | book | 22.35 | book | book | book | 1 | 22.35 |
| 6 | 1 | 1 | nicebook | 9999.00 | nicebook | nicebook | nicebook | 6 | 9999.00 |
| 2 | 2 | 5 | ball | 32.25 | ball | ball | ball | 2 | 32.25 |
| 7 | 2 | 3 | googlebook | 25.30 | book | googlebook | book | 7 | 25.30 |
| 3 | 3 | 5 | NULL | 3.23 | NULL | NULL | NULL | 3 | 3.23 |
| 4 | 3 | 5 | macbook | 3.23 | book | macbook,listbook | book,book | 4,5 | 3.23,2.30 |
+----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+

A comparison between goods_descs here and above will make it clear

Next, you can group by goods_price

mysql> select *,group_concat(goods_name) goods_names,group_concat(goods_desc) goods_descs,group_concat(id) ids,group_concat(goods_price) goods_prices from goods group by s_id,goods_desc,goods_price;
+----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+
| id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices |
+----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+
| 1 | 1 | 5 | book | 22.35 | book | book | book | 1 | 22.35 |
| 6 | 1 | 1 | nicebook | 9999.00 | nicebook | nicebook | nicebook | 6 | 9999.00 |
| 2 | 2 | 5 | ball | 32.25 | ball | ball | ball | 2 | 32.25 |
| 7 | 2 | 3 | googlebook | 25.30 | book | googlebook | book | 7 | 25.30 |
| 3 | 3 | 5 | NULL | 3.23 | NULL | NULL | NULL | 3 | 3.23 |
| 5 | 3 | 5 | listbook | 2.30 | book | listbook | book | 5 | 2.30 |
| 4 | 3 | 5 | macbook | 3.23 | book | macbook | book | 4 | 3.23 |
+----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+

Here, when grouping multiple fields, you only need to understand that the fields after the grouping order are grouped according to the contents of the previous 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.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links

You may also be interested in:

Detailed explanation of whether the MySQL database should use foreign key constraints
Why is the disk space still occupied after deleting table data in MySQL?
In-depth analysis of MySQL index data structure
MySQL essential basics: grouping function, aggregate function, grouping query detailed explanation
MySQL query statement grouped by time
Detailed explanation of the group by statement in MySQL database group query
Example of implementing grouping and deduplication in MySQL table join query
MySQL group query optimization method
MySQL data aggregation and grouping

<<: How to Rename a Group of Files at Once on Linux

>>: Vue realizes picture switching effect