How to combine Duplicate data into single Data MySql

Issue

I was wondering if what is this called or is this even possible in MySql, or what should I do to achieve this. This is what Mysql Table Looks Like:

enter image description here

Desired Output:

enter image description here

Solution

you can use a query like this:

SELECT  customerid 
 , CONCAT( SUM(CAST(item as INTEGER)), 'pcs,', SUBSTRING_INDEX(item, ',', -1) ) as item
FROM myitems
GROUP BY SUBSTRING_INDEX(item, ',', -1);

Sample

MariaDB [bernd]> SELECT * FROM myitems;
+----+------------+---------------+
| id | customerid | item          |
+----+------------+---------------+
|  1 |         15 | 7pcs, Car     |
|  2 |         15 | 2pcs, Car     |
|  3 |         15 | 3pcs, Engine  |
|  4 |         15 | 2pcs, Engine  |
|  5 |         15 | 5pcs, Exhaust |
+----+------------+---------------+
5 rows in set (0.07 sec)

MariaDB [bernd]> SELECT customerid 
    ->      , CONCAT( SUM(CAST(item as INTEGER)), 'pcs,', SUBSTRING_INDEX(item, ',', -1) ) as item
    ->     FROM myitems
    ->     GROUP BY SUBSTRING_INDEX(item, ',', -1);
+------------+---------------+
| customerid | item          |
+------------+---------------+
|         15 | 9pcs, Car     |
|         15 | 5pcs, Engine  |
|         15 | 5pcs, Exhaust |
+------------+---------------+
3 rows in set, 5 warnings (0.02 sec)

MariaDB [bernd]> 

Answered By – Bernd Buffen

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published