描述
题目:现在运营举办了一场比赛,收到了一些参赛申请,表数据记录形式如下所示,现在运营想要统计每个性别的用户分别有多少参赛者,请取出相应结果
示例:user_submit
device_id | profile | blog_url |
2138 | 180cm,75kg,27,male | http:/url/bigboy777 |
3214 | 165cm,45kg,26,female | http:/url/kittycc |
6543 | 178cm,65kg,25,male | http:/url/tiger |
4321 | 171cm,55kg,23,female | http:/url/uhksd |
2131 | 168cm,45kg,22,female | http:/urlsydney |
根据示例,你的查询应返回以下结果:
gender | number |
male | 2 |
female | 3 |
select
case
when profile like '%,male' then 'male'
when profile like '%,female' then 'female'
else 'null gender'
end as gender,
count(1)
from user_submit
group by gender;