MySQL 5.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table movie (id int primary key auto_increment, type text);
insert into movie (type) values ('drama'),('comedy, romance'),('comedy'),('thriller, short'),('drama, romance');
select type from movie;


type


drama

comedy, romance

comedy

thriller, short

drama, romance


select id, type all_types,
    round((length(type)-length(replace(type,', ','')))/length(', ')+1) num_types,
    num,
    substring_index(substring_index(type, ', ', num),', ',-1) type
from movie
join (
    select 1 num union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
) nums on num <= (length(type)-length(replace(type,', ','')))/length(', ')+1;


id
all_types
num_types
num
type


1
drama
1
1
drama

2
comedy, romance
2
1
comedy

3
comedy
1
1
comedy

4
thriller, short
2
1
thriller

5
drama, romance
2
1
drama

2
comedy, romance
2
2
romance

4
thriller, short
2
2
short

5
drama, romance
2
2
romance


select type, count(*) from (
    select substring_index(substring_index(type, ', ', num),', ',-1) type
    from movie
    join (
        select 1 num union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
    ) nums on num <= (length(type)-length(replace(type,', ','')))/length(', ')+1
) movie_types
group by type;


type
count(*)


comedy
2

drama
2

romance
2

short
1

thriller
1

id	all_types	num_types	num	type
1	drama	1	1	drama
2	comedy, romance	2	1	comedy
3	comedy	1	1	comedy
4	thriller, short	2	1	thriller
5	drama, romance	2	1	drama
2	comedy, romance	2	2	romance
4	thriller, short	2	2	short
5	drama, romance	2	2	romance