I am trying to get the top 10 Genres from the IMDB Movies database (CSV format). Normally it is an easy task all you have to do is group and count. But in this database genre is given in quite a weird format. Eg: Action|Adventure|Fantasy|Sci-Fi
I have tried counting using like function but with this, I can only count 1 genre which I use as the keyword in like fucntion.
If it were to be python I could have split all genre types using the split function and then it would be easy. But in SQL I don't know how would I split or how can I run all genres in like function. I also tried using IN function but it gave me a syntax error i.e can't be used like this.
select genres, count(movie_title) AS Number_of_Movie
from imdb_movies_column_drop
group by genres
order by Number_of_Movie DESC
limit 10;
Using this code gives me this output
The output that I need is:
| Genres | Number_of_Movie |
|---|---|
| Comedy | 75 |
| Romance | 70 |
| Action | 66 |
| Adventure | 66 |
And so on.