SQL query for commulative GPA calculation
hi friends,
i have table following creation sql script
createtable [dbo].[test_enrollment](
[enrollment_id] [int] notnull,
[course_offering_id] [int] notnull,
[student_id] [int] notnull,
[dept_id] [int] notnull,
[course_code] [varchar](10)notnull,
[credit] [int] null,
[section_id] [int] notnull,
[semester] [varchar](10)notnull,
[acad_level] [varchar](30)notnull,
[acad_year] [varchar](50)notnull,
[date_enrolled] [date] null,
[status] [varchar](30)null,
[total_mark] [decimal](5, 2)null,
[grade_letter] [varchar](2)null,
[qpe] [float] null,
[honor] [float] null,
[instructor_id] [int] null,
[username] [varchar](50)null
)on [primary]
go
below sql insert above table’s data
insertinto test_enrollment(enrollment_id,course_offering_id,student_id,dept_id,course_code,credit,section_id,
semester,acad_level,acad_year,date_enrolled,[status],total_mark,grade_letter,qpe,honor,instructor_id,username
)values(877,356,104,15,'ird 109',3,395,'one','year 2','2014-2015','2015-10-07','normal',91.00,'a+',4,12,100,'mohamoud')
insertinto test_enrollment(enrollment_id,course_offering_id,student_id,dept_id,course_code,credit,section_id,
semester,acad_level,acad_year,date_enrolled,[status],total_mark,grade_letter,qpe,honor,instructor_id,username
)values(877,356,104,15,'ird 110',3,395,'one','year 2','2014-2015','2015-10-07','normal',87.00,'a',3.7,11.1,100,'mohamoud')
insertinto test_enrollment(enrollment_id,course_offering_id,student_id,dept_id,course_code,credit,section_id,
semester,acad_level,acad_year,date_enrolled,[status],total_mark,grade_letter,qpe,honor,instructor_id,username
)values(877,356,104,15,'ird 111',3,395,'one','year 2','2014-2015','2015-10-07','normal',59.00,'d+',1,3,100,'mohamoud')
insertinto test_enrollment(enrollment_id,course_offering_id,student_id,dept_id,course_code,credit,section_id,
semester,acad_level,acad_year,date_enrolled,[status],total_mark,grade_letter,qpe,honor,instructor_id,username
)values(593,322,104,15,'ird 741',3,395,'two','year 2','2014-2015','2015-10-07','normal',74.00,'b-',2.5,7.5,100,'mohamoud')
insertinto test_enrollment(enrollment_id,course_offering_id,student_id,dept_id,course_code,credit,section_id,
semester,acad_level,acad_year,date_enrolled,[status],total_mark,grade_letter,qpe,honor,instructor_id,username
)values(593,324,104,15,'ird 742',3,395,'two','year 2','2014-2015','2015-10-07','normal',65.00,'c+',2,6,100,'mohamoud')
insertinto test_enrollment(enrollment_id,course_offering_id,student_id,dept_id,course_code,credit,section_id,
semester,acad_level,acad_year,date_enrolled,[status],total_mark,grade_letter,qpe,honor,instructor_id,username
)values(878,359,104,15,'ird 745',3,395,'one','year 2','2014-2016','2015-10-07','normal',68.00,'c+',2,6,100,'mohamoud')
insertinto test_enrollment(enrollment_id,course_offering_id,student_id,dept_id,course_code,credit,section_id,
semester,acad_level,acad_year,date_enrolled,[status],total_mark,grade_letter,qpe,honor,instructor_id,username
)values(878,361,104,15,'ird 747',3,395,'one','year 2','2014-2016','2015-10-07','normal',95.00,'a+',4,12,100,'mohamoud')
i trying calculate gpa , commulative gpa (cgpa) student , formula of gpa sum(honor)/sum(credit) , formula of cgpa average of sum(honor)/sum(credit) grouped semester , wrote below query calculating gpa correctly wrongly calculating cgpa. please me correct cgpa shown in below desired result set table.
select x.student_id,avg(x.gpa)as gpa,avg(y.gpa)as'commulative gpa'
from
(
select student_id,sum(honor)/sum(credit) gpa,acad_year,semester from test_enrollment where student_id=104
groupby student_id,acad_year,semester
) x
join
(
select student_id,sum(honor)/sum(credit) gpa,acad_year,semester from test_enrollment where student_id=104
groupby student_id,acad_year,semester
) y on x.acad_year=y.acad_year and x.semester=y.semester groupby x.student_id,x.acad_year,x.semester orderby x.acad_year,x.semester
current result set (incorrect commulative gpa)
| student_id | gpa | commulative gpa |
| 104 | 2.9 | 2.9 |
| 104 | 2.25 | 2.25 |
| 104 | 3 | 3 |
desired result set
| student_id | gpa | commulative gpa |
| 104 | 2.9 | 2.9 |
| 104 | 2.25 | 2.575 |
| 104 | 3 | 2.716 |
thanks in advance
best regards,
mohamoud
and here solution older version of sql server:
;with mycte ( select student_id,sum(honor)/sum(credit) gpa, acad_year, semester, row_number() on (partition student_id order acad_year, semester) rn from test_enrollment where student_id=104 group student_id,acad_year,semester ) select *, (select avg(gpa) mycte i.rn <= o.rn) mycte o go
 | ronen ariely [personal site] [blog] [facebook] |
SQL Server > Getting started with SQL Server
Comments
Post a Comment