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
Lets say
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
etc.
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.
Thanks!!!
select USERS.BUILDINGID,
USERS.GRADE_LEVEL,
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
FROM
PS.USERS USERS
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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.