get count number of values with comma seprated

Question

I have a column in SQL which is a comma separated list (ie cats,dogs,cows,) I need to count the number of cats,dogs and cows in it using only mysql

id | Name
1  | dog,cat
2  | cow,cat
3  | dog,cat,cow

Answer 1

You first effort should go into fixing your data model. You should have a separate table to store the id/name relationships, where each tuple would be stored on a separate row, like:

id    name
----------
1     dog
1     cat
2     cow
2     cat
3     dog
3     cat
3     cow

This would make your queries much simpler and more efficient.

As of your current set up: if the list of values is fixed, you can use find_in_set() and conditional aggregation:

select
    sum(find_in_set('cat', name) > 0) no_cats
    sum(find_in_set('cow', name) > 0) no_cows,
    sum(find_in_set('dog', name) > 0) no_dogs
from mytable