CODEWITHSUNDEEP
sqloracle-database
EvilEddie
EvilEddie

Reputation: 1035

Assign Students a Grade and get totals for each Grade

I have the following select statement. I have schools, courses, students and marks (1-100). I want to assign a grade (A,B,C,D) and then get the total number of students in each grade. My results only show the count of each mark though and not the count of the grade

select          
    schools.name as school_name,            
    courses.name as course,   
        CASE
             WHEN   ((studentgrades.averageScore / 50  * 100)  > 79)
                  THEN 'A' 
                  WHEN   ((studentgrades.averageScore / 50  * 100)  < 80) 
                         AND ((studentgrades.averageScore / 50  * 100)  >64)
                  THEN 'B' 
                  WHEN   ((studentgrades.averageScore / 50  * 100)  < 80) 
                         AND ((studentgrades.averageScore / 50  * 100)  >64)
                  THEN 'C' 
                  WHEN   ((studentgrades.averageScore / 50  * 100)  < 50)
                  THEN 'D'                         
          END as grade,             
    count(*)                 
from
    students,
    studentgrades,
    schools,
    courses
where
    studentgrades.studentid = students.studentid
    and studentgrades.schoolid = students.schoolid     
    and studentgrades.schoolid = schools.school_number 
    and courses.id = studentgrades.coursesid
    and studentgrades.averageScore is not null 
    and schools.name = 'St. Joe School'           
group by schools.name,  courses.name,standardsgrades.averageScore

What I get now is the count of individual marks (e.g. 5 students got 88%, 3 got 85% etc)

School Name     Course        Grade     Count        
St. Joe School  MATH 30         A         5
St. Joe School  MATH 30         A         3
St. Joe School  MATH 30         A         2
St. Joe School  MATH 30         A         1
St. Joe School  MATH 30         A         1
St. Joe School  MATH 30         A         2 
St. Joe School  MATH 30         A         3
St. Joe School  MATH 30         B         2
St. Joe School  MATH 30         B         5
St. Joe School  MATH 30         B         2
St. Joe School  MATH 30         B         1 
St. Joe School  MATH 30         B         2

What I want to see it is the total of count of each grade (A,B,C,D)

School Name     Course        Grade     Count        
St. Joe School  MATH 30         A         30
St. Joe School  MATH 30         B         20
St. Joe School  MATH 30         C         10
St. Joe School  MATH 30         D         5

Upvotes: 0

Views: 1097

Answers (3)

Pritam Parashuram Thakur
Pritam Parashuram Thakur

Reputation: 133

Just do select on your existing query as like below:

select school_name, course,
grade,
sum(grade)
(select          
schools.name as school_name,            
courses.name as course,   
    CASE
         WHEN   ((studentgrades.averageScore / 50  * 100)  > 79)
              THEN 'A' 
              WHEN   ((studentgrades.averageScore / 50  * 100)  < 80) 
                     AND ((studentgrades.averageScore / 50  * 100)  >64)
              THEN 'B' 
              WHEN   ((studentgrades.averageScore / 50  * 100)  < 80) 
                     AND ((studentgrades.averageScore / 50  * 100)  >64)
              THEN 'C' 
              WHEN   ((studentgrades.averageScore / 50  * 100)  < 50)
              THEN 'D'                         
      END as grade,             
count(*)                 
from
students,
studentgrades,
schools,
courses
where
studentgrades.studentid = students.studentid
and studentgrades.schoolid = students.schoolid     
and studentgrades.schoolid = schools.school_number 
and courses.id = studentgrades.coursesid
and studentgrades.averageScore is not null 
and schools.name = 'St. Joe School'           
group by schools.name,  courses.name,standardsgrades.averageScore)
group by grade order by grade;

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You need to group by the calculated grade instead of the average score. You can do this using a CTE, subquery or repeating the whole expression in the group clause.

I find CTE based solution bit easy to read:

with cte
as (
    select schools.name as school_name,
        courses.name as course,
        case when ((studentgrades.averageScore / 50 * 100) > 79) then 'A' when ((studentgrades.averageScore / 50 * 100) < 80)
                and ((studentgrades.averageScore / 50 * 100) > 64) then 'B' when ((studentgrades.averageScore / 50 * 100) < 80)
                and ((studentgrades.averageScore / 50 * 100) > 64) then 'C' when ((studentgrades.averageScore / 50 * 100) < 50) then 'D' end as grade
    from students
    join studentgrades on studentgrades.studentid = students.studentid
        and studentgrades.schoolid = students.schoolid
    join schools on studentgrades.schoolid = schools.school_number
        and studentgrades.schoolid = schools.school_number
    join courses on courses.id = studentgrades.coursesid
    where studentgrades.averageScore is not null
        and schools.name = 'St. Joe School'
    )
select school_name,
    course,
    grade,
    count(*)
from cte
group by school_name,
    course,
    grade;

Also, always use modern explicit join syntax instead of the old comma based joins.

Upvotes: 1

Jacob H
Jacob H

Reputation: 2505

Put the query you have into a view and then query it with another GROUP BY and SUM.

SELECT SchoolName, Course, Grade, SUM(Count)
FROM YourNewView
GROUP BY SchoolName, Course, Grade

Upvotes: 0

Related Questions