How to combine Duplicate data into single Data MySql

Question

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:

Desired Output:

Answer 1

Your table structures wont allow (anything near as easy). A better format of your table might be more like

id   customerid   item      qty
1    15           Car       7  
2    15           Car       2
3    15           Engine    3
4    15           Engine    2
5    15           Exhaust   5

Then, you could get what you are looking for easily such as

select
      t.customerid,
      t.item,
      sum( t.qty )
   from
      YourTable t
   group by
      t.customerid,
      t.item

This WOULD give your your

customerid  item      qty
15          Car       9
15          Engine    5
15          Exhaust   5

Having a properly formatted table vs trying to have a description requires parsing numeric values vs rest of description is never a good solution such as what you have here. Too many opportunities for bad answers.

Answer 2

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]>