如何在单个案例语句中对两列求和

Question

下面的查询返回 2 行，但实际上我只需要一行;

select Datename(month, m.CreatedDate) as [Ay], sum(case when h.Cinsiyet=1 then 1 else 0 end) as [Group1], sum(case when h.Cinsiyet=2 then 1 else 0 end) as [Group2] from Muayene.Muayene m with(nolock)
join Ortak.Hasta h with(nolock) on m.HastaTc = h.HastaTc
group by  h.Cinsiyet, Datename(month, m.CreatedDate)

结果：

MonthName Group1 Group2
April     4500   0
April     0      9000

预期成果：

MonthName Group1 Group2
April     4500   9000

我知道我可以用另一个选择语句包装查询并按月分组并对这些结果求和。但它效率不高，看起来很脏的代码。

如何在不做另一个总和语句的情况下使出一个技巧来获得预期的结果？

Answer 1

FIx GROUP BY ：

select Datename(month, m.CreatedDate) as [Ay],
       sum(case when h.Cinsiyet = 1 then 1 else 0 end) as [Group1],
       sum(case when h.Cinsiyet = 2 then 1 else 0 end) as [Group2]
from Muayene.Muayene m join
     Ortak.Hasta h 
     on m.HastaTc = h.HastaTc
group by Datename(month, m.CreatedDate);