troubleshooting Question

Adding a total based on the query results

Avatar of Basssque
Basssque asked on
Oracle DatabaseSQL
24 Comments1 Solution49 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 24 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 24 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros