CODEWITHSUNDEEP
sqljoincountgroup-bysum
user1901398
user1901398

Reputation: 61

calculating gpa sql

to calculate SGPA of a students in table the formula is sum of 3*gradepoint of every course registered by a specific student in specific semester and dividing this sum by no.of courses.

i am having a table named All_Registration having fields and data as followed 

╔════════════╦═════════════╦════════╦════════════╗
║ STUDENT_ID ║  SEMESTER   ║ COURSE ║ GRADEPOINT ║
╠════════════╬═════════════╬════════╬════════════╣
║ i090170    ║ Fall 2000   ║    200 ║ 3.67       ║
║ i090170    ║ Fall 2000   ║    201 ║ 2.67       ║
║ i090170    ║ Fall 2000   ║    203 ║ 2          ║
║ i090170    ║ Fall 2000   ║    205 ║ 4          ║
║ i090170    ║ Fall 2000   ║    209 ║ 3.67       ║
║ i090170    ║ Fall 2000   ║    211 ║ 3          ║
║ i090170    ║ spring 2000 ║    200 ║ 3.67       ║
║ i090170    ║ spring 2000 ║    201 ║ 2.67       ║
║ i090170    ║ spring 2000 ║    203 ║ 2          ║
║ i090170    ║ spring 2000 ║    205 ║ 4          ║
║ i090170    ║ spring 2000 ║    209 ║ 3.67       ║
╚════════════╩═════════════╩════════╩════════════╝

Note: Student can take multiple courses in semester i.e 5 or 6

what i have done so is that i have calculated the no. of courses taken by student in a semester

select abc
from
(
    select Student_Id,Semester, count(distinct Courses) as abc
    from All_Registration B
    group by Student_Id,Semester
)A;

this returns the count of distinct courses taken by student in a semester

now i want to calculate SGPA. i want to make a query which can do the following for every semester of each student :

"sum of all courses(grade point * 3 of a course))/no.of courses per semester of that student "

Upvotes: 0

Views: 3795

Answers (1)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10073

Try this,

select sum(Gradepoint*3)/count (distinct course) as gpa
from All_Registration group by Student_Id,Semester 

or

select (3*sum(Gradepoint))/count (distinct course) as gpa
from All_Registration group by Student_Id,Semester

Sample Result :

GPA
36927.03
12049.02

Note: I didnt include all of your data. So this result will differ from what you expect

SQL Fiddle Demo

Upvotes: 1

Related Questions