select 
case
when profile like '%,male' then 'male'
when profile like '%,female' then 'female'
end gender,
count(device_id)
from user_submit
group by gender;