SQL Group By中重复行的大小写合计

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

   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

瞎猜

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

应该为您提供distinct space.id和一个标志,以确定它是否有效。