带CASE的SUM计数SQL GROUP BY中的重复行

Question

我正在尝试对符合特定条件的所有项目执行SUM，比如：

SELECT l.Building_Name, 
SUM(CASE WHEN s.Date >= '20180930' THEN 1 ELSE 0 END) Validated,
COUNT(DISTINCT s.id) Total
FROM Lab_Space s
JOIN Locations l ON s.Building_Code = l.Building_Code
GROUP BY l.Building_Name

那里的COUNT是正确的，会说20，因为我可以把DISTINCT s.id放在那里。然而，我的SUM最终得到了大约1500。这是因为当我这样做时，JOIN行被重复多次，因此SUM对每一行进行计数。

如何在确保仅适用于不同行的情况下执行类似的SUM/CASE？

s.id    l.building_name  s.date
1       JF               2018-11-10
1       JF               2018-11-10
2       JF               2018-12-12

因此，如果我有这样的数据，我会得到正确的计数2，但validate会说3，因为1的id由于执行JOIN 而出现两次

Answer 1

如果您认为合适，可以编辑临时表的此代码。

create table #temp_Lab_Space
([Date] date null
,Building_Code  int null
)
create table #temp_Locations 
( Building_Code  int null
,Building_Name varchar(10) null
)

insert into #temp_Lab_Space values
('2018-11-10',1)
,('2018-11-10', 1)
,('2018-12-12' , 1)
insert into #temp_Locations values
(1, 'JF')

select Building_Name, 
SUM(CASE WHEN Date >= '20180930' THEN 1 ELSE 0 END) Validated,
COUNT(DISTINCT Building_Code) Total
from (
select  distinct l.Building_Name, s.Building_Code, s.Date
,Rank_1 = rank() over(partition by l.Building_Name order by s.Date asc)
FROM #temp_Lab_Space s
JOIN #temp_Locations l ON s.Building_Code = l.Building_Code
) a
group by Building_Name

Answer 2

猜测

select      l.Building_Name
, count(s.Id)
, sum(s.Validated)
from        Locations          l
cross apply (   select      s.Id
, max(case
when s.Date >= '20180930' then 1
else 0
end) as Validated
from        Lab_Space s
where       s.Building_Code = l.Building_Code
group by    s.Id) s
group by l.Building_Name

应该为您提供不同的space.id和一个是否已验证的标志。