Calculated Field (GPA)

I have a table and I need to have a calculated field which calculates GPA based on the letter grade they have, and only those grades that have subject as CHEM or BIO.

here is a sample table:

Term

ID

LastName

FirstName

CRN

Subject

LetterGrade

Calc GPA

20072

1

Doe

John

1234

CHEM

B

20072

1

Doe

John

3214

BIO

A

20072

1

Doe

John

4321

LAW

B

20072

2

Bauer

Jack

1234

CHEM

A

20072

2

Bauer

Jack

3214

BIO

C

20072

2

Bauer

Jack

5467

FIN

B

A = 4.0

B = 3.0

C = 2.0

D = 1.0

Here ya go:

Code Snippet

declare @.t table (Term char(5), ID int, LastName varchar(50), FirstName varchar(50),

CRN int, Subject varchar(20), LetterGrade char(1))

insert into @.t

select '20072', 1, 'Doe', 'John', 1234, 'CHEM', 'B'

union all select '20072', 1, 'Doe', 'John', 3214, 'BIO', 'A'

union all select '20072', 1, 'Doe', 'John', 4321, 'LAW', 'B'

union all select '20072', 2, 'Bauer', 'Jack', 1234, 'CHEM', 'A'

union all select '20072', 2, 'Bauer', 'Jack', 3214, 'BIO', 'C'

union all select '20072', 2, 'Bauer', 'Jack', 5467, 'FIN', 'B'

declare @.GradeEquiv table (LetterGrade char(1), NumericGrade numeric(2, 1))

insert into @.GradeEquiv

select 'A', 4.0

union all select 'B', 3.0

union all select 'C', 2.0

union all select 'D', 1.0

select t.Term, t.ID, t.LastName, t.FirstName, avg(ge.NumericGrade) as GPA

from @.t t

inner join @.GradeEquiv ge

on t.LetterGrade = ge.LetterGrade

where Subject in ('CHEM', 'BIO')

group by t.Term, t.ID, t.LastName, t.FirstName

|||

Try:

;with Grade

as

(

select '20072' as Term, 1 as ID, 'Doe' as LastName, 'John' as FirstName, 1234 CRN, 'CHEM' as [Subject], 'B' as LetterGrade

union all select '20072', 1, 'Doe', 'John', 3214, 'BIO', 'A'

union all select '20072', 1, 'Doe', 'John', 4321, 'LAW', 'B'

union all select '20072', 2, 'Bauer', 'Jack', 1234, 'CHEM', 'A'

union all select '20072', 2, 'Bauer', 'Jack', 3214, 'BIO', 'C'

union all select '20072', 2, 'Bauer', 'Jack', 5467, 'FIN', 'B'

)

select

Term,

ID,

LastName,

FirstName,

CRN,

[Subject],

LetterGrade,

AVG(

case LetterGrade

when 'A' then 4.0

when 'B' then 3.0

when 'C' then 2.0

when 'D' then 1.0

end

) OVER(partition by Term, ID) as GPA

from

Grade

where

[Subject] in ('CHEM', 'BIO');

AMB

|||

Thanks guys, I'll try those out now.

|||

Is there a way to still show the other classes, like LAW for instance, but to still have the GPA from the other calculated classes?

|||

Try:

;with Grade

as

(

select '20072' as Term, 1 as ID, 'Doe' as LastName, 'John' as FirstName, 1234 CRN, 'CHEM' as [Subject], 'B' as LetterGrade

union all select '20072', 1, 'Doe', 'John', 3214, 'BIO', 'A'

union all select '20072', 1, 'Doe', 'John', 4321, 'LAW', 'B'

union all select '20072', 2, 'Bauer', 'Jack', 1234, 'CHEM', 'A'

union all select '20072', 2, 'Bauer', 'Jack', 3214, 'BIO', 'C'

union all select '20072', 2, 'Bauer', 'Jack', 5467, 'FIN', 'B'

)

select

Term,

ID,

LastName,

FirstName,

CRN,

[Subject],

LetterGrade,

SUM(

case

when [Subject] in ('CHEM', 'BIO') and LetterGrade = 'A' then 4.0

when [Subject] in ('CHEM', 'BIO') and LetterGrade = 'B' then 3.0

when [Subject] in ('CHEM', 'BIO') and LetterGrade = 'C' then 2.0

when [Subject] in ('CHEM', 'BIO') and LetterGrade = 'D' then 1.0

end

) OVER(partition by Term, ID) /

nullif(

SUM(

case

when [Subject] in ('CHEM', 'BIO') then 1

else 0

end

) OVER(partition by Term, ID), 0) as GPA

from

Grade;

AMB