Ad

MySQL - How Can I Group Music Together When The Names Are Similar?

- 1 answer

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!

Ad

Answer

Try:

SELECT COUNT(name) no,
       TRIM(SUBSTRING_INDEX(name, '-', 1)) namee
FROM track
GROUP BY namee

Example: https://onecompiler.com/mysql/3xt3bfev6

Ad
source: stackoverflow.com
Ad