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

 

signature   ronen ariely
 [personal site]    [blog]    [facebook]



SQL Server  >  Getting started with SQL Server



Comments

Post a Comment

Popular posts from this blog

SQL server replication error Cannot find the dbo or user defined function........

hi there, i ran problem while deploying sql 2008 transactional replication between 2 servers lets assume server , b. actually created  both distributor , publisher on server , after created subscriber pointed server b, launch replication monitor , found big red cross. when click on tab "distributor subscriber history" error says "cannot find either column "dbo" or user defined-function or aggregate "dbo.ufnleadingzeros", or name ambiguous". can body regarding issue. regards, ishuv hi, you need compare data , others reseved, index size, unused should not compared. differs. here server - data - 87896 kb server b - data - 78152 kb server data size includes publication tables, can't compare directly. need check article article know exact data size. SQL Server  >  ...
Read more

BIT Version

  hi,     knows how find out ther bit version in sql  , windows os (32 or 64)   regards, s.balavenkatesh   microsoft sql server 2005 - 9.00.1399.06 (intel x86) oct 14 2005 00:33:37 copyright (c) 1988-2005 microsoft corporation standard edition on windows nt 5.2 (build 3790: service pack 2)     intel x86)   -- says sql server 32 bit   madhu SQL Server  >  SQL Server Tools
Read more

Admin Permissions

hi need create new 'role' following permissions, 1. access sql server logs. 2. access maintenance plans ( can avoid access) 3. access activity monitor 4. normal access product databases , not system db´s need guidance on how it, can point me in right direction please? thanks 1.grant  execute on xp_readerrorlog [domainname\ad user or group]; 2. http://technet.microsoft.com/en-us/library/ms191002.aspx 3.view server state permission 4. make user member of dbo_owner database role. best regards,uri dimant sql server mvp, http://sqlblog.com/blogs/uri_dimant/ ms sql optimization: ms sql development , optimization ms sql consulting: large scale of database , data cleansing remote dba services: improves ms sql database performance sql server integration services: business intelligence SQL Server  >  ...
Read more