views:

194

answers:

2

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?

+2  A: 
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
Quassnoi
What is outer apply mean?
Malfist
ru.MontlyRent can't be used there.
Malfist
It's like an outer join, with the difference that it uses an inner query instead of a subquery.
Maximilian Mayerl
This means that the query results are "applied" to each record from the outer query. It's like a `SELECT` clause subquery but can return multiple records.
Quassnoi
A: 

What is the meaning of the sum of the unitMonthlyRent times the number of tenants, for some partiicular rental unit (COUNT(t.ID) * ru.MonthlyRent )?

Is it the case that all you are trying to do is see the difference between the total potential rent from all untis versus the expected rent (From only occcupied units) ? If so, then try this

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
Charles Bretana
No, this doesn't work. r.PropertyID in the left join isn't even in scope.
Malfist
Yes, See that now, the join should be to rental unit in your schema
Charles Bretana