Counting in a subquery 1

[mtepfer]

Have 2 queries I am trying to join together. I used the second one as a subquery in the first but am not getting the results I need. Not sure how to get it to segregate based on proeprty since you can't use Group By in the subquery with more than one item returned.

The Output from the Queries is this :

Query 1 (Total)

Property Total
Azeele 10
Bay 40
Bethune 150


Query 2 (Leased)

Property Leased
Azeele 10
Bay 36
Bethune 147

Want to combine them to read :
Property Total Leased
Azeele 10 10
Bay 40 36
Bethune 150 147

Here is my query to attempt this:

	SELECT    p.saddr1, count(u.scode)as Total, 
                     (SELECT count(p.saddr1) 
		      FROM yardi_prod.dbo.PROPERTY AS P
                      LEFT OUTER JOIN yardi_prod.dbo.UNIT   
                      AS u ON p.HMY = u.HPROPERTY AND
                      u.EXCLUDE = 0 LEFT OUTER JOIN			      yardi_prod.dbo.H8PROP AS h8p ON
                      h8p.HPROP = p.HMY AND SPROGRAMDEFAULT
                      = 'P' INNER JOIN 
                      Yardi_prod.dbo.unit_status AS ub ON 
                      u.HMY = ub.hUnit AND UPPER
                      (ub.sStatus) IN ('OCCUPIED NO 
                      NOTICE', 'NOTICE RENTED', 'NOTICE   
                      UNRENTED') AND DATEADD(day, - 1, 
                      DATEADD(month, DATEDIFF(month, 0, 
                      GETDATE()) + 1, 0)) BETWEEN 
                       ub.dtStart AND ISNULL
                      (ub.dtEnd, '01/01/2100')	
	    WHERE p.hmy in ('26','27','28')) as Leased							

	FROM yardi_prod.dbo.PROPERTY AS p INNER JOIN
             yardi_prod.dbo.UNIT AS u ON p.HMY =   
             u.HPROPERTY AND u.EXCLUDE = 0 LEFT OUTER JOIN
             yardi_prod.dbo.H8PROP AS h8p ON h8p.HPROP =  
             p.HMY AND h8p.SPROGRAMDEFAULT = 'P'
	where p.hmy in ('26','27','28') 
	Group By p.saddr1

When I run this I get :

Property Total Leased
Azeele 10 193
Bay 40 193
Bethune 150 193

So it is summing up all items (10,36,147) in the lease instead of grouping by property.

Any suggestions on a path to take would be appreciated, I am sure this can be done but know I am not looking at it the right way.

Mike

 

[gmmastros]

It's difficult to tell from your query what's actually going on. However, you should be able to return this information easily by using a derived table approach. The generic syntax would be...

Select A.Property, A.Total, B.Leased
From   (
       [!]Your Query that returns totals[/!]
       ) As A
       Inner Join 
         (
         [!]Your query that returns Leased[/!]
         ) As B
         On A.Property = B.Property

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PatriciaObreja]

Try something like this see if it works:

...WHERE p.hmy in ('26','27','28')
AND p.Property=u.Property) as Leased...

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[mtepfer]

Thanks George, that worked perfectly