计算男性、女性和总数 [英] Count male, female and total
本文介绍了计算男性、女性和总数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想从学生表中计算指定特定年份的男性、女性和学生总数.我希望结果是否可以以表格形式显示:
I want to count male, female and total students from Student table for a specific year specified. I wish if the result could be displayed in the form:
====================================
| Label | Value | Year |
====================================
| Male | 0 | 2013 |
| Female | 23 | 2013 |
| Total | 23 | 2013 |
====================================
如果指定年份没有男/女匹配,则查询应显示 0.知道我怎样才能做到这一点吗?
The query should display 0 if there is no male/female matching for the specified year. Any idea how I can make this happen?
提前致谢
推荐答案
考虑以下查询:
select
max(registeredYear) as year,
count(case when gender='Male' then 1 end) as male_cnt,
count(case when gender='Female' then 1 end) as female_cnt,
count(*) as total_cnt
from student
where registeredYear = 2013
group by registeredYear;
结果是这样的:
Year male_cnt female_cnt total_cnt
---- -------- ---------- ---------
2013 0 23 23
您可以将此结果转换为您想要的形式.如果您想在查询中执行此操作,则可以这样做:
You can transform this result into the form you want. If you want to do it within a query, then you can do it like this:
with t as (
select
max(registeredYear) as year,
count(case when gender='Male' then 1 end) as male_cnt,
count(case when gender='Female' then 1 end) as female_cnt,
count(*) as total_cnt
from student
where registeredYear = 2013
group by registeredYear)
select 'Male', male_cnt as male, year from t
union all
select 'Female', female_cnt as male, year from t
union all
select 'Total', total_cnt as male, year from t
;
这篇关于计算男性、女性和总数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文