Is it possible to add two columns to the Oracle query at the bottom based on my criteria below?
I need the sum of the total instances where 'Y' exists in S_CT_STU_SPED_X.SPECIALEDUCATION for each USERS.GRADE_LEVEL record and output a total in a new column grouped by USERS.GRADE_LEVEL only
This is contradictory to the current set of results that I'm calling because I need to group the existing query statements by buildingid AND grade_level
buildingid 1 and 2 have grade_levels 6 and 7
buildingid 3 has grade levels of 6, 7, and 8
buildingid 4 has grade levels of 8, 9, 10, 11, 12
and building 5 has grade levels of 1 through 12
I'd need to total all instances of 'Y' in records that exist in S_CT_STU_SPED_X.SPECIALEDUCATION for grade_level 7 across building 1, 2 and 5
The same for grade_level 8 in building 3, 4 and 5
I was thinking something like a partition by statement would work but can't quite figure it out.
The second new column would total the number of records in STUDENTS.ETHNICITY not equal to '04' by buildingid only, NOT grade level
It seems that the group by statement that applies to the entire query is whats tripping me up the most here.
I'd also like to avoid having duplicate rows containing the same results in these 2 new columns if possible.
count(USERS.ETHNICITY) as Total_USERS_Per_Grade,
count(case when USERS.ETHNICITY != '04' then 1 end) as Ethnic_User_Count,
count(case when S_NY_STU_CODE_X.CODEEDUCATION = 'Y' then 1 end) as CODE_Ed_User_Count,
round(count(case when USERS.ETHNICITY != '04' then 1 end)/count(USERS.ETHNICITY)*100,2)||'%' as Ethnic_User_Count
LEFT JOIN PS.S_NY_STU_CODE_X S_NY_STU_CODE_X
ON PS.USERS.DCID = PS.S_NY_STU_CODE_X.USERSDCID
group by BUILDINGID, GRADE_LEVEL
order by BUILDINGID