I'd like to put the column of MySQL aggregation next to it.

This is a MySQL aggregation question.

Table 1:

Number of user items
Mr. A, apple 1
Mr. A, Banana 4
Mr. A, Orange 2

Results:

Number of user itemsNumber of itemsNumber of items
Mr. A, apple, banana, orange, 2

What SQL statements should I run from Table 1 to achieve the results I want above?

Thank you for your cooperation.

mysql sql

2022-09-30 14:27

1 Answers

I created the test table as below and filled in the data appropriately.The UNIQUE constraint is not set in the (user, product) column.

>desctest;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| User | varchar(20) | YES | | NULL | |
| Products | varchar(20) | YES | | NULL | |
| Number | int(11) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+

>select* from test;
+-------------+--------------+--------+
| US>User|Products|Number|
+-------------+--------------+--------+
| Mr. A | Apple | 1 |
| Mr. A | Banana | 4 |
| Mr. A | Amber | 2 |
| Mr. A | Apple | 10 |
| Mr. B | Apple | 5 |
| Mr. C | Banana | 3 |
| Mr. C | Amber | 6 |
| Mr. C | Banana | 4 |
+--------------+--------------+--------+

Create a GROUP_CONCAT per user in the subquery.

>SELECT User, GROUP_CONCAT(XSEPARATOR'')AS Product List
FROM(
  SELECT User, CONCAT (Products, '', SUM (Number)) ASX
  FROM test GROUP BY user, product
AS list GROUP BY users;

+--------------+-----------------------------+
| User | Product List |
+--------------+-----------------------------+
| Mr. A | Orange 2 Banana 4 Apple 11 |
| Mr. B | Apple 5 |
| Mr. C | Orange 6 Banana 7 |
+--------------+-----------------------------+

2022-09-30 14:27

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656