Group By statement problem

[Crystalguru]

Hello,

I am trying to complete the following SQL.
SELECT DISTINCT
1 AS SOLVE_ORDER,
MIN(COM.REGION) AS REGION,
COM.PROPERTY_ID AS PROPERTY_ID,
MIN(COMS.PROPERTY_NAME) AS PROPERTY_NAME,
MIN(COM.PROPERTY_UNIT_COUNT) AS PROPERTY_UNIT_COUNT,
TTYPE.DATE_DAY AS DATE_DAY,
SUM(TTYPE.OCCUPIED) AS OCCUPIED,
sum(occupied)/sum(distinct property_unit_count)*100 AS WA_OCCUPANCY,
TDEF.TIER AS TIER
FROM
COMMUNITIES COM
TIME_SERIES_BY_UNIT_TYPE TTYPE ,
TIERDEFS TDEF
WHERE
COM.PROPERTY_ID = TTYPE.PROPERTY_ID
AND TRIM(COM.PROPERTY_REGION) = TRIM(TDEF.REGION)
AND TTYPE.DATE_DAY >= TO_DATE('1/1/2002','MM/DD/YYYY')
AND TO_CHAR(TTYPE.DATE_DAY,'DY') = 'SUN'
AND COM.PROPERTY_ACTIVE_FLAG = 1 AND
TTYPE.DATE_DAY BETWEEN TDEF.BEGINDATE AND ENDDATE
and
sum(occupied)/sum(distinct property_unit_count)*100 between tdef.tierlow and tdef.tierhigh
GROUP BY COMMUNITIES.PROPERTY_ID, TIME_SERIES_BY_UNIT_TYPE.DATE_DAY, TDEF.TIER

My problem in within the WHERE statement on the --sum(occupied)/sum(distinct property_unit_count)*100--a group function is not allowed in the where statement. I am not sure how to do the comparision now?? Any ideas?

thanks carrie

 

[swampBoogie]

Use having, it is used to restrict records within groups.

GROUP BY COMMUNITIES.PROPERTY_ID, TIME_SERIES_BY_UNIT_TYPE.DATE_DAY, TDEF.TIER
having
sum(occupied)/sum(distinct property_unit_count)*100 between tdef.tierlow and tdef.tierhigh

Specifying distinct for a query containing a group by clause is meaningless.

 

[Crystalguru]

Here's what I have:

SELECT
1 AS SOLVE_ORDER,
MIN(COM.REGION) AS REGION,
COM.PROPERTY_ID AS PROPERTY_ID,
MIN(COMS.PROPERTY_NAME) AS PROPERTY_NAME,
MIN(COM.PROPERTY_UNIT_COUNT) AS PROPERTY_UNIT_COUNT,
TTYPE.DATE_DAY AS DATE_DAY,
SUM(TTYPE.OCCUPIED) AS OCCUPIED,
sum(occupied)/sum(distinct property_unit_count)*100 AS WA_OCCUPANCY,
TDEF.TIER AS TIER
FROM
COMMUNITIES COM
TIME_SERIES_BY_UNIT_TYPE TTYPE ,
TIERDEFS TDEF
WHERE
COM.PROPERTY_ID = TTYPE.PROPERTY_ID
AND TRIM(COM.PROPERTY_REGION) = TRIM(TDEF.REGION)
AND TTYPE.DATE_DAY >= TO_DATE('1/1/2002','MM/DD/YYYY')
AND TO_CHAR(TTYPE.DATE_DAY,'DY') = 'SUN'
AND COM.PROPERTY_ACTIVE_FLAG = 1 AND
TTYPE.DATE_DAY BETWEEN TDEF.BEGINDATE AND ENDDATE
and
sum(occupied)/sum(distinct property_unit_count)*100 between tdef.tierlow and tdef.tierhigh
GROUP BY COM.PROPERTY_ID, TTYPE.DATE_DAY, TDEF.TIER
having
sum(occupied)/sum(distinct property_unit_count)*100 between tdef.tierlow and tdef.tierhigh

Now, I am getting the error "not a GROUP BY expression".
I don't actually want to limit what is returned. If date_day between begindate and enddate and wa_occupancy between tierlow and tierhigh then give me the corresponding TIER... I just can't get it to do this.

Thanks

 

[r937]

you're probably getting the error on sum(distinct property_unit_count)

some databases will not let you use a column alias

substitute the actual expression, and you then have

SUM(TTYPE.OCCUPIED)
/ sum(distinct MIN(COM.PROPERTY_UNIT_COUNT) )*100

you may have to re-think what it is you're summing

as near as i can figure out, the number of distinct minimum values is going to be 1, so basically i think this is the same as a count of rows, which makes the entire expression look like an average, except that 100 is a factor in the divisor, and so it might be a percent, except that in percentage calculations, 100 is usually a factor in the dividend...


rudy