MySQL - How can I group music together when the names are similar?

Question

I would like to be able to return a single line when the name of some musics are the same or similar, as for example this case: music with similar names

You can see that the names are the same with an extension like " - JP Ver." or something like that, I would like to be able to group them in one row with the first column incrementing the whole.

My current request to return these lines is as follows:

select count(id) number, name, sec_to_time(floor(sum(duration) / 1000)) time
from track
where user_id = 'value'
group by name, duration
order by number desc, time desc;

I would like to get a result like this

Thank you for reading and responding! I wish you all a good day!

Answer 1

Use GROUP_CONCAT Here is a proof of concept script. You can add your other columns. I have grouped by the first 4 letters. You will probably want to use more.

CREATE TABLE track (
        idd INT,
        nam CHAR(50),
        tim INT
        );
INSERT INTO track VALUES (1,'Abba 1',5);
INSERT INTO track VALUES (2,'Abba 2',6);
INSERT INTO track VALUES (3,'Beta 1',12);
INSERT INTO track VALUES (4,'Beta 4',8);

SELECT
        LEFT(nam,4) AS 'Group',
        COUNT(idd) AS 'Number',
        GROUP_CONCAT(DISTINCT idd ORDER BY idd ASC SEPARATOR ' & ') AS IDs,
        GROUP_CONCAT(DISTINCT nam ORDER BY nam ASC SEPARATOR ', ') AS 'track names',
        SUM(tim) AS 'total time'
FROM track
GROUP BY LEFT(nam,4);
DROP TABLE track;

Output

Group   Number  IDs     track names     total time
Abba    2       1 & 2   Abba 1, Abba 2  11
Beta    2       3 & 4   Beta 1, Beta 4  20