Calculate GPA - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculate GPA

Author

Topic

gj0519
Starting Member

13 Posts

Posted - 2009-04-16 : 11:54:05

So far I have 2 queries, 1 gives me the students GPA value for each class, and the other gives me the count of classes per student. I need to be able to combine my 2 queries to do the calculation. Now I need to divide my 1st qry by my 2nd query. I am trying it by using a derived table but so far no luck.

1st Qry

Select 
studentid
    Sum((Case [110_letter]
      When 'A+' then 4
      When 'A' Then 4
      When 'A-' Then 4
      When 'B+' Then 3
      When 'B' Then 3
      When 'B-' Then 3
      When 'C+' Then 2
      When 'C' Then 2
      When 'C-' Then 2
      When 'D+' Then 1
      When 'D' Then 1
      When 'D-' Then 1
      When 'F' Then 0 End)) As GPA
 from grade2s
Where StudentID = '1001'
and [111_Status] = 'A'
Group By StudentID

2nd Qry

Select studentid,COUNT(ClassNum) Classes
From Grade2S
Where StudentID = '1001' 
and [111_Status] = 'A'
Group by Studentid

Thanks,

GJ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 13:56:28

[code]
Select
studentid
Sum((Case [110_letter]
When 'A+' then 4
When 'A' Then 4
When 'A-' Then 4
When 'B+' Then 3
When 'B' Then 3
When 'B-' Then 3
When 'C+' Then 2
When 'C' Then 2
When 'C-' Then 2
When 'D+' Then 1
When 'D' Then 1
When 'D-' Then 1
When 'F' Then 0 End)) As GPA,
COUNT(ClassNum) Classes,
Sum((Case [110_letter]
When 'A+' then 4
When 'A' Then 4
When 'A-' Then 4
When 'B+' Then 3
When 'B' Then 3
When 'B-' Then 3
When 'C+' Then 2
When 'C' Then 2
When 'C-' Then 2
When 'D+' Then 1
When 'D' Then 1
When 'D-' Then 1
When 'F' Then 0 End))*1.0/ NULLIF(COUNT(ClassNum),0) AS YourQty
from grade2s
Where StudentID = '1001'
and [111_Status] = 'A'
Group By StudentID
[/code]

gj0519
Starting Member

13 Posts

Posted - 2009-04-16 : 14:11:53

Thanks again for the help. What I was trying was way more in depth than needed.

GJ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 14:18:54

ok..welcome

gj0519
Starting Member

13 Posts

Posted - 2009-04-16 : 15:54:19

Thanks for the help.
Here is what we did following the code you gave me.
1 question, can you clarify why we had to mulitply by 1.0. We see what it does, but why wouldn't a decimal display automaticaly.

GJ

select grade2s.studentid, tbl.gpascale *1.0 / NULLIF(tbl.classcount,0) AS GPA 
from grade2s join 

(select studentid, COUNT([111_status]) as classcount,
sum(Case when [110_Flag] IS Null Or [110_Flag] = ''
Then (case [110_Letter]
WHEN 'A' then '4'
WHEN 'B' then '3'
WHEN 'C' then '2'
WHEN 'D' then '1'
Else 0 end)

ELSE (Case [110_Flag] 

WHEN 'A' then '4'
WHEN 'B' then '3'
WHEN 'C' then '2'
WHEN 'D' then '1'
Else 0 End)End) as gpascale

From Grade2s
Where [110_Status] = 'A' and 
(([110_Flag] is not Null and [110_Flag] <> '') or
([110_Letter] is not Null and [110_Letter] <> ''))
group by studentid) as tbl 

on Grade2S.StudentID = tbl.studentid
group by Grade2S.studentid, tbl.gpascale, tbl.classcount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources