Crystalguru
Technical User
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 "main" 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
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 "main" 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