select a.gender,count(a.gender) as number
from(select device_id,
       case when substring_index(profile,',',-1)='male' then 'male'
       when substring_index(profile,',',-1)='female' then 'female'
       end as gender
       from user_submit) a
group by gender