Help me IM SO LOST - 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

General SQL Server Forums

New to SQL Server Programming

Help me IM SO LOST

Author

Topic

sertay
Starting Member

7 Posts

Posted - 2011-11-10 : 14:37:47

ok here is my problem i have to join 3 tables. stuinfo, grades, courseinfo

select distinct stuinfo.id, stuinfo.last + ', ' + stuinfo.first as Student, grades.grade, CourseInfo.hours
from grades
join stuinfo as stuinfo
on (StuInfo.id = grades.id)
join CourseInfo as CourseInfo
on (grades.courseID = CourseInfo.courseID)

something like that my problem is using sum() with a case statement
i need grades.grade which are A B C D F to change over to 4 3 2 1 0
then add them up for each student when i put my case statement in the select statement it errors

sum(Case when grade IS Null
Then (case grade
WHEN 'A' then '4'
WHEN 'B' then '3'
WHEN 'C' then '2'
WHEN 'D' then '1'
Else 0 end)

ELSE (Case grade

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

then next i need to pull the course hours for each course and add them up for each student
which i have no idea how to do

then i need to totalgrade/totalhours for each student for gpa

i have no idea what to do on these. i missed a week of school and not getting much help

if you would please help me out and also help me understand id lov ya forever hahaha

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-10 : 15:06:34

Have you spoken with your lecturer/teacher/tutor?

btw, you don't need 3 case statement, you just need one. Just a SUM(CASE Grade WHEN .... END)
Also, lose that DISTINCT. It's probably not necessary, is inefficient and is learning bad practice.

--
Gail Shaw
SQL Server MVP

sertay
Starting Member

7 Posts

Posted - 2011-11-10 : 15:38:53

select stuinfo.id, stuinfo.last + ', ' + stuinfo.first as Student, grades.grade, CourseInfo.hours,
sum(Case grade
WHEN 'A' then '4'
WHEN 'B' then '3'
WHEN 'C' then '2'
WHEN 'D' then '1'
Else 0 en) as totalgrade

does that look right

from grades
join stuinfo as stuinfo
on (StuInfo.id = grades.id)
join CourseInfo as CourseInfo
on (grades.courseID = CourseInfo.courseID)

Kristen
Test

22859 Posts

Posted - 2011-11-10 : 21:21:19

If you are using SUM you need to use GROUP BY on the columns in your SELECT which are outside the aggregate function(s)

I presume you need the total hours? so SUM(CourseInfo.hours) AS TotalHours

That leaves these "non aggregate" columns in the select:


stuinfo.id, stuinfo.last + ', ' + stuinfo.first as Student

(I've taken grades.grade out - presumably you don't want that listed, per student, you just want them totalled up as A=4, B=3 etc)

So you need to add to the bottom:


GROUP BY stuinfo.id, stuinfo.last + ', ' + stuinfo.first

to match your SELECT "non aggregate" columns.

Thus the SELECT becomes


select stuinfo.id, stuinfo.last + ', ' + stuinfo.first as Student,
SUM(CourseInfo.hours) AS TotalHours,
sum(Case grade 
WHEN 'A' then '4'
WHEN 'B' then '3'
WHEN 'C' then '2'
WHEN 'D' then '1'
Else 0 end) as totalgrade

try that and report back with any questions to make sure you've understood it - otherwise you won't be learning it if we do it for you and you don't understand it

sertay
Starting Member

7 Posts

Posted - 2011-11-11 : 12:19:17

so when you use funtions you have to group by the things that arent functions?

so my statement would look like

select stuinfo.id, stuinfo.last + ', ' + stuinfo.first as Student,
SUM(CourseInfo.hours) AS TotalHours,
sum(Case grade
WHEN 'A' then '4'
WHEN 'B' then '3'
WHEN 'C' then '2'
WHEN 'D' then '1'
Else 0 end) as totalgrade

from grades
join stuinfo as stuinfo
on (StuInfo.id = grades.id)
join CourseInfo as CourseInfo
on (grades.courseID = CourseInfo.courseID)

GROUP BY stuinfo.id, stuinfo.last + ', ' + stuinfo.first

cool cool

so for the gpa would i add in the select statement gpa=totalgrade/totalhours ????

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 12:24:55

select *, gpa=totalgrade/totalhours
from (
select stuinfo.id, stuinfo.last + ', ' + stuinfo.first as Student,
SUM(CourseInfo.hours) AS TotalHours,
sum(Case grade
WHEN 'A' then '4'
WHEN 'B' then '3'
WHEN 'C' then '2'
WHEN 'D' then '1'
Else 0 end) as totalgrade

from grades
join stuinfo as stuinfo
on (StuInfo.id = grades.id)
join CourseInfo as CourseInfo
on (grades.courseID = CourseInfo.courseID)
GROUP BY stuinfo.id, stuinfo.last + ', ' + stuinfo.first
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sertay
Starting Member

7 Posts

Posted - 2011-11-11 : 12:32:38

huh?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 12:38:49

You can't use totalgrade totalhours in that select statement as thyey are not defined - you would have to repeat the calculation.
This is making the select a derived table so that the outer select can reference anything in the inner select result.

Try it and see
an equivalent statement is

;with cte as
(
select stuinfo.id, stuinfo.last + ', ' + stuinfo.first as Student,
SUM(CourseInfo.hours) AS TotalHours,
sum(Case grade
WHEN 'A' then '4'
WHEN 'B' then '3'
WHEN 'C' then '2'
WHEN 'D' then '1'
Else 0 end) as totalgrade

from grades
join stuinfo as stuinfo
on (StuInfo.id = grades.id)
join CourseInfo as CourseInfo
on (grades.courseID = CourseInfo.courseID)
GROUP BY stuinfo.id, stuinfo.last + ', ' + stuinfo.first
)
select *, gpa=totalgrade/totalhours
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sertay
Starting Member

7 Posts

Posted - 2011-11-11 : 12:41:41

now would that cause gpa to be a seperate table our with it actually add into the table?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 12:44:22

It's a single resultset.
Why not try it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sertay
Starting Member

7 Posts

Posted - 2011-11-11 : 12:47:19

im just trying to understand?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 13:02:27

try running these
select *, k=i+j
from
(
select i=1, j=2
) a

select i=1, j=2, k=i+j

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sertay
Starting Member

7 Posts

Posted - 2011-11-29 : 15:43:37

ok nigel thank you so much for your help i just have one more question
Why is the a needed at the end of the sub statement? I dont understand that part and having trouble finding out information bout it

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-29 : 17:44:56

its an alias.

The more verbose (possibly more readable too) syntax would be:


SELECT
    a.*
    , a.i + a.j AS k
FROM
    (
    SELECT
        1 AS i
        , 2 AS j
    )
    AS a

Basically you are giving your derived result set a name (in this case a) with which to refer to it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources