假设我有下表
claim_date person_type
------------------------
01-01-2012 adult
05-05-2012 adult
12-12-2012 adult
12-12-2012 adult
05-05-2012 child
05-05-2012 child
12-12-2012 child
当我执行以下查询时:
select
claim_date,
sum(case when person_type = 'adult' then 1 else 0 end) as "nbr_of_adults",
sum(case when person_type = 'child' then 1 else 0 end) as "nbr_of_children"
from my_table
group by claim_date
;
我在这里得到这个结果:
claim_date nbr_of_adults nbr_of_children
---------------------------------------------
01-01-2012 1 0
05-05-2012 1 2
12-12-2012 2 1
我希望收到的是成人的最大数量(此处:2)和儿童的最大数量(此处:2)。
有没有办法通过单个查询来实现这一点?感谢您的任何提示。
使用派生表获取计数,然后选择最大值:
select max(nbr_of_adults) max_adults,
max(nbr_of_children) max_children
from
(
select
sum(case when person_type = 'adult' then 1 else 0 end) as "nbr_of_adults",
sum(case when person_type = 'child' then 1 else 0 end) as "nbr_of_children"
from my_table
group by claim_date
) a
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)