Sub query returning value...

[Crystalguru]

I am having trouble returning a simple SUM statement. The 2nd query is my main query. As the query is written down below, it will return the Unit Count for each community * the occurences in the times_series table (112790 was my total,the actual is 7901). I can not use distinct due to multiple communities having identical unit counts. (tried that). So, I created a simple query, and got the correct unit count this is it-
SELECT SUM(PROPERTY_UNIT_COUNT)
FROM S.COMMUNITIES
WHERE PROPERTY_REGION = 'Dallas'
AND PROPERTY_ACTIVE_FLAG = 1
AND PROPERTY_REGIONAL_MANAGER <>'All Lease Up')

This query returns the correct number. How can I use this query in my &quot;main&quot; query to return the correct number? I tried using it in the From and Select, but am new to this and need some assistance.


select
1 as SOLVE_ORDER
,c.PROPERTY_REGION AS PROPERTY_REGION
,C.PROPERTY_REGION AS PROPERTY_ACTUAL_REGION
,0 AS PROPERTY_ID
, MIN(CONCAT('Weighted Avg - ', c.property_region)) as Property_Name
,'zzzzz' as PROPERTY_TAG
,sum(c.property_unit_count AS PROPERTY_UNIT_COUNT*/
,ts.date_day as DATE_DAY
,SUM( TS.OCCUPIED) AS OCCUPIED
,sum( ts.occupied)/SUM(C.PROPERTY_UNIT_COUNT)*100 AS WA_OCCUPANCY
from
s.communities c,
s.time_series_by_unit_type ts
WHERE
C.PROPERTY_ID = TS.PROPERTY_ID
AND TS.DATE_DAY >= TO_DATE('1/1/2002','MM/DD/YYYY')
AND TO_CHAR(TS.DATE_DAY,'DY') = 'SUN'
AND C.PROPERTY_REGIONAL_MANAGER <> 'All Lease Up'
and c.property_region = 'Dallas'
AND C.PROPERTY_ACTIVE_FLAG = 1
AND DATE_DAY = ('6-JAN-2002')
GROUP BY C.PROPERTY_REGION
,C.PROPERTY_REGION
,TS.DATE_DAy

thanks

 

[YipYeppp]

I really don't know, it is supposed to work, your code is well done.
Do you think it may be your &quot&quot; taht is not there on the line of your SUM, and the */ that is still there too??

 

[Crystalguru]

I didn't clean it up very well. Those are gone on my side. How do I insert the select sum(property_unit_count)from communities query in with the MAIN query? That's where I am lost....

 

[swampBoogie]

You can put a scalar subquery in the select list like this

select
1 as SOLVE_ORDER
,c.PROPERTY_REGION AS PROPERTY_REGION
,C.PROPERTY_REGION AS PROPERTY_ACTUAL_REGION
,0 AS PROPERTY_ID
, MIN(CONCAT('Weighted Avg - ', c.property_region)) as Property_Name
,'zzzzz' as PROPERTY_TAG
,(SELECT SUM(PROPERTY_UNIT_COUNT)
FROM S.COMMUNITIES
WHERE PROPERTY_REGION = 'Dallas'
AND PROPERTY_ACTIVE_FLAG = 1
AND PROPERTY_REGIONAL_MANAGER <>'All Lease Up')
,ts.date_day as DATE_DAY
,SUM( TS.OCCUPIED) AS OCCUPIED
,sum( ts.occupied)/SUM(C.PROPERTY_UNIT_COUNT)*100 AS WA_OCCUPANCY
from
s.communities c,
s.time_series_by_unit_type ts
WHERE
C.PROPERTY_ID = TS.PROPERTY_ID
AND TS.DATE_DAY >= TO_DATE('1/1/2002','MM/DD/YYYY')
AND TO_CHAR(TS.DATE_DAY,'DY') = 'SUN'
AND C.PROPERTY_REGIONAL_MANAGER <> 'All Lease Up'
and c.property_region = 'Dallas'
AND C.PROPERTY_ACTIVE_FLAG = 1
AND DATE_DAY = ('6-JAN-2002')
GROUP BY C.PROPERTY_REGION
,C.PROPERTY_REGION
,TS.DATE_DAy

It seems to me though that you are trying to retrive to much in one query. You will get the same value multiple times so maybe it would be better to select from s.communities in one query and the join in one query.