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.


Comments

Popular posts from this blog

How to remove text and logo OR add Overflow on Android ActionBar using AppCompat on API 8? -

html - How to style widget with post count different than without post count -

url rewriting - How to redirect a http POST with urlrewritefilter -