Change the column value depends on occurrence count

There is the table:

NAME   GENRE
book1  genre1
book2  genre2
book3  genre1
book3  genre2
book4  genre1
etc

So, we can have a book with only one or with many genres. From this table I would like to obtain this table

NAME   GENRE
book1  genre1
book2  genre2
book3  mixed_genres
book4  genre1

So in the new table if the book has only one genre, we keep this genre, if the book has many genres we change genre value by “mixed_genres”.

There is any way to do this? Many thanks for any advice!

Answer

You can use aggregation:

select book,
       (case when min(genre) = max(genre) then max(genre)
             else 'mixed genres'
        end) as genre
from t
group by book;

You can also list out all the genres:

select book, group_concat(genre) as genre
from t
group by book;