SQL 嵌套或相关查询

Question

我正在尝试根据具有特定变量的相同数量的服务器计数的子查询计算我拥有的服务器数量。我可以返回实体的资产总数，但当我返回符合条件的资产总数时，它给我的是总计数，而不是特定业务的计数。

SELECT DATEPART(week, GETDATE()) AS WEEK, bt.TranslatedParentBusiness AS [Parent Business], count(cars.ServerID) as [Count of Servers], 
(Select count(auth.serverID) from AuthTracking auth where [AUTH STATUS] like 'PASSED')
FROM     ASSETS.dbo._CARS AS cars INNER JOIN
              ASSETS.dbo.BU_Translation AS bt ON cars.CARS_SubBusiness = bt.[CARS Sub Business]
GROUP BY bt.TranslatedParentBusiness    

Answer 1

SELECT
    DATEPART(week, GETDATE()) AS WEEK,
    bt.TranslatedParentBusiness AS [Parent Business],
    count(cars.ServerID) as [Count of Servers], 
    count(CASE WHEN [AUTH STATUS] = 'PASSED' THEN auth.serverID END)
FROM     ASSETS.dbo._CARS AS cars INNER JOIN
              ASSETS.dbo.BU_Translation AS bt ON cars.CARS_SubBusiness = bt.[CARS Sub Business]
GROUP BY bt.TranslatedParentBusiness