• Status: Solved
• Priority: Medium
• Security: Public
• Views: 66

# Calculating percentages per course - Oracle Query

I have 2 problems here.
#1
Courses aren't grouping together.  My results have the same course listed several times with different count(*) per row.  Each identical course_name and course_number should be grouped into 1

#2
I need to get the percentage of ethnicity not equal to 04 per course.  So the total students.ethnicity not equal to 04 divided by the total instances of  students.student_number "for each" individual course.   The percentage I'm calculating in the query below only returns 0% or 100% which isn't correct.

Thanks so much!!!

``````SELECT
CC.COURSE_NUMBER,
COURSES.COURSE_NAME,
count(*) as COURSE_ENROLLMENT,
ROUND(COUNT(CASE WHEN STUDENTS.ETHNICITY != '04' THEN 1 end)/ count(*) * 100) || '%' Minority

FROM
STUDENTS
LEFT JOIN CC ON CC.STUDENTID = STUDENTS.ID
LEFT JOIN S_CT_STU_DEMOGRAPHICS_X ON STUDENTS.DCID = S_CT_STU_DEMOGRAPHICS_X.STUDENTSDCID
LEFT JOIN COURSES ON CC.COURSE_NUMBER = COURSES.COURSE_NUMBER

WHERE
CC.TERMID IN ('2600','2601','2602')
AND STUDENTS.SCHOOLID IN ('61', '62')
AND STUDENTS.ENROLL_STATUS = 0

GROUP BY
CC.COURSE_NUMBER,
STUDENTS.ETHNICITY,
COURSES.COURSE_NAME

ORDER BY CC.COURSE_NUMBER
``````
0
Basssque
1 Solution

Commented:
>>My results have the same course listed several times

It is probably because you are grouping on ETHNICITY.  You will end up with one course_number and course_name row for each ETHNICITY value.

If you want a copy/paste solution, please provide sample data and expected results.
0

Commented:
Can you provide some sample data (dummy it up if it's proprietary) for that query and what you expect the percentages to be?
0

Author Commented:
I just had to remove ethnicity from the group by clause and everything now works as expected.  Thanks so much!
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.