sql - Mysql group by in a CSV field -

what's best way perform group statement in csv-like column using sql (mysql)?

table products color +-------------+--------------+ | product id  | color        | +-------------+--------------+ | 120         | red, blue    | | 121         | green, black |  | 122         | red, black   |  +-------------+--------------+ 

from table above need count how many times color appears , return this:

+-------------+-------+ | color       | count | +-------------+-------+ | red         | 2     | | blue        | 1     |  | green       | 1     |  | black       | 2     |  +-------------+-------+ 

is possible without normalize database?

if can't change table structure, can create table lists colors, use query:

select colors.color, count(*)   products inner join colors   on find_in_set(colors.color, replace(products.color, ' ', ''))>0 group   colors.color 

see working here. please notice can't optimized because can't make use on index.