Cannot perform an aggregate function on a subquery

Question

Can someone help me with this query?

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

I'm having problems with the second sum, it won't let me do it. Evidently SUM won't work on subqueries, but I need to calculate the expected rent (MonthlyRent if there is a tenant assigned to the RentalUnit's id, 0 of they're not). How can I make this work?

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

Here's a test script to check:

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