select
    case
		# 重点是like后面应当跟'%,male'而不是'%male'，
		# 具体原因是前者更加精确，因为female字符串也包含了male子串，所有profile字符串都like '%male'，
		# 只有like '%,male%'的才是男性
        when profile like '%,male' then 'male' else 'female'
    end as gender,
    count(*) as number
from user_submit
group by gender;