MYSQL - Concatenating two group concat functions into a list

Question

So i'm working with this data set based on a sneaker store, and what i need to do, is to get rid of duplicate fields containing shoe models that only differ in size. What i'm trying to do, is to merge the columns that contain seperately the size and the amount on hand of that size (as in "US-9: 4 pairs, US-10: 11 pairs, US-7: 6 pairs etc.), and then merge the rows into a list with each size and it's corresponding quantity. This is the best i've been able to do so far

SELECT * FROM shoes;
+------------+------------+--------------+------+----------+
| primary_id | product_id | shoe         | size | quantity |
+------------+------------+--------------+------+----------+
|          1 |          1 | Nike Cortez  |   38 |        1 |
|          2 |          1 | Nike Cortez  |   41 |        4 |
|          3 |          2 | Adidas Samba |   38 |        5 |
|          4 |          2 | Adidas Samba |   39 |        3 |
|          5 |          3 | Puma Clyde   |   40 |        4 |
|          6 |          3 | Puma Clyde   |   41 |        2 |
+------------+------------+--------------+------+----------+

SELECT product_id, shoe, CONCAT(GROUP_CONCAT(DISTINCT size SEPARATOR ', '), " : ", GROUP_CONCAT(DISTINCT quantity SEPARATOR ', ')) as `quantity_of_given_sizes` FROM shoes GROUP BY product_id;
+------------+--------------+-------------------------+
| product_id | shoe         | quantity_of_given_sizes |
+------------+--------------+-------------------------+
|          1 | Nike Cortez  | 38, 41 : 1, 4           |
|          2 | Adidas Samba | 38, 39 : 3, 5           |
|          3 | Puma Clyde   | 40, 41 : 2, 4           |
+------------+--------------+-------------------------+

score 0 · Answer 1 · answered Mar 10 '22 at 23:24

You had it almost right.

First you need to concat the columns and then then the rows.

As you don't have a US column i omitted it

SELECT 
    product_id
    , shoe
    , GROUP_CONCAT(DISTINCT CONCAT( `size`,':' ,`quantity`) SEPARATOR  '|') as `quantity_of_given_sizes`
FROM shoes 
GROUP BY product_id, shoe;

product_id | shoe         | quantity_of_given_sizes
---------: | :----------- | :----------------------
         1 | Nike Cortez  | 38:1|41:4              
         2 | Adidas Samba | 38:5|39:3              
         3 | Puma Clyde   | 40:4|41:2

db<>fiddle here

MYSQL - Concatenating two group concat functions into a list

1 Answers1