MySQL Grouping with value separations not working

I have a MySQL table that records item with item, make, model & serial number as follows :

item table

Then I need to group items by the item_name and make. And also needs to show all models & serial nos in the group summary as follows :

Desired Output

I used the following simple query to do this.

SELECT item_name, make, model, serial_no, SUM(qty) AS qty FROM item GROUP BY item_name, make 

But the query outs the following output without showing all models & serial nos under the relevant item_name & make.

Incorrect Output

Some models & serial nos were missing. What may going wrong. Can anyone help ?

>Solution :

You can achieve the Desired_Output by using GROUP_CONCAT(). Here is the modified sql

SELECT
    item_name,
    make,
    GROUP_CONCAT(DISTINCT model ORDER BY model) AS model,
    GROUP_CONCAT(DISTINCT serial_no ORDER BY serial_no) AS serial_no,
    SUM(qty) AS qty
FROM item
GROUP BY item_name, make;

The DISTINCT ensures model and serial_no are included only once, while GROUP_CONCAT concatenates the results after they are sorted using ORDER BY