无法对子查询执行聚合函数

Question

有人可以帮我解决这个问题吗？

SELECT p.OwnerName, SUM(ru.MonthlyRent) AS PotentinalRent,  SUM(
    (SELECT COUNT(t.ID) * ru.MonthlyRent FROM tblTenant t 
      WHERE t.UnitID = ru.ID)
    ) AS ExpectedRent
 FROM tblRentalUnit ru
LEFT JOIN tblProperty p ON p.ID = ru.PropertyID
GROUP BY p.OwnerName

我在处理第二笔款项时遇到了问题，它不允许我这样做。显然 SUM 不适用于子查询，但我需要计算预期租金（如果有租户分配给 RentalUnit 的 id，则为 MonthlyRent，其中 0 个不是）。我怎样才能做到这一点？

Answer 1

SELECT  p.OwnerName, SUM(ru.MonthlyRent) AS PotentialRent, SUM(cnt) AS ExpectedRent
FROM    tblRentalUnit ru
LEFT JOIN
        tblProperty p
ON      p.ID = ru.PropertyID
OUTER APPLY
        (
        SELECT  COUNT(t.id) * ru.MonthlyRent AS cnt
        FROM    tblTenant t
        WHERE   t.UnitID = ru.ID
        ) td
GROUP BY p.OwnerName

这是一个要检查的测试脚本：

WITH    tblRentalUnit AS
        (
        SELECT  1 AS id, 100 AS MonthlyRent, 1 AS PropertyID
        UNION ALL
        SELECT  2 AS id, 300 AS MonthlyRent, 2 AS PropertyID
        ),
        tblProperty AS
        (
        SELECT  1 AS id, 'Owner 1' AS OwnerName
        UNION ALL
        SELECT  2 AS id, 'Owner 2' AS OwnerName
        ),
        tblTenant AS
        (
        SELECT  1 AS id, 1 AS UnitID
        UNION ALL
        SELECT  2 AS id, 1 AS UnitID
        )
SELECT  p.OwnerName, SUM(ru.MonthlyRent) AS PotentialRent, SUM(cnt) AS ExpectedRent
FROM    tblRentalUnit ru
LEFT JOIN
        tblProperty p
ON      p.ID = ru.PropertyID
OUTER APPLY
        (
        SELECT  COUNT(t.id) * ru.MonthlyRent AS cnt
        FROM    tblTenant t
        WHERE   t.UnitID = ru.ID
        ) td
GROUP BY p.OwnerName

Answer 2

对于某些特定的租赁单位 (COUNT(t.ID) * ru.MonthlyRent )，unitMonthlyRent 乘以租户数量的总和意味着什么？

您是否只想查看所有单位的潜在总租金与预期租金（仅使用的单位）之间的差异？如果是这样，那么试试这个

Select p.OwnerName, 
   Sum(r.MonthlyRent) AS PotentinalRent,  
   Sum(Case t.Id When Null Then 0 
         Else r.MonthlyRent End) ExpectedRent
From tblRentalUnit r
    Left Join tblTenant t 
      On t.UnitID = r.ID
    left Join tblProperty p
       On p.ID = r.PropertyID)
Group By p.OwnerName