February 20, 2009 at 8:03 am
I had a student_marks table and the columns in the table are:
---------------------------------------
COURSE_ID MARKS_TYPE MARKS STUDENT_ID
---------------------------------------
ASM001 Mid-term 23 3015
ASM001 End-term 31 3015
OB001 Mid-Term 19 3001
ASM001 Project 37 3002
---------------------------------------
Now am running a query:
SELECT dbo.student_marks.student_ID, dbo.student_marks.course_code, dbo.course_details.course_credit, SUM(dbo.student_marks.marks),
Grade =
CASE
WHEN (SELECT SUM(dbo.student_marks.marks) from dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 100 and 91 then 'A+'
WHEN (SELECT SUM(dbo.student_marks.marks) from dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 90 and 83 then 'A'
WHEN (SELECT SUM(dbo.student_marks.marks) from dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 75 and 82 then 'B+'
WHEN (SELECT SUM(dbo.student_marks.marks) from dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 67 and 74 then 'B'
WHEN (SELECT SUM(dbo.student_marks.marks) from dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 59 and 66 then 'C+'
WHEN (SELECT SUM(dbo.student_marks.marks) from dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 51 and 58 then 'C'
WHEN (SELECT SUM(dbo.student_marks.marks) from dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 43 and 50 then 'D+'
WHEN (SELECT SUM(dbo.student_marks.marks) from dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 35 and 42 then 'D'
WHEN 'Deniel of Grade' then 'DN'
WHEN 'Absent' then 'ABS'
WHEN 'With Draw From Course' then 'WFC'
WHEN 'Audit Course' then 'A+'
ELSE 'F'
END
FROM dbo.student_marks INNER JOIN dbo.course_details ON dbo.student_marks.course_code = dbo.course_details.course_code
GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code, dbo.course_details.course_credit
to get the following result:
-----------------------------------
COURSE_ID STUDENT_ID Total* Grade
-----------------------------------
ASM001 3015 54 C
ASM001 3002 37 D
OB001 3001 19 F
-----------------------------------
here *Total of ASM001 = 23 + 31 = 54
But am getting the following error:
Server: Msg 8624, Level 16, State 25, Line 1
Internal SQL Server error.
Please help...am in great need!!
Daipayan
February 20, 2009 at 9:18 am
I'm guessing you have some data that is throwing this off.
Do the subselect's work fine by themselves?
I'd think you'd be able to CASE the SUM and not need the select.
create table MyTable
( Student varchar(10)
, gradeno int
)
go
insert MyTable select 'Steve', 50
insert MyTable select 'Steve', 100
insert MyTable select 'Bob', 50
insert MyTable select 'Bob', 80
insert MyTable select 'Bob', 90
insert MyTable select 'Bob', 100
insert MyTable select 'Andy', 40
insert MyTable select 'Andy', 50
insert MyTable select 'Andy', 60
insert MyTable select 'Andy', 70
go
select Student
, avg( gradeno) 'grades'
, case
when avg(gradeno) >= 90 then 'A'
when avg(gradeno) BETWEEN 80 and 89 then 'A'
else 'F'
end 'Grade'
from MyTable
group by Student
go
drop table MyTable
February 20, 2009 at 9:52 am
Start with something small and straightforward. Work with it until you understand it and you're comfortable with how it works, and progress from there.
Building a large complex query and hoping that it might work makes no more sense than constructing a large complex building and hoping that it might stay up 😀
This little snippet might help. It's not the "best way" to write the query, but that's not the point. It's simple and easy to understand, and also easy to modify to make it do what you want.
-- make a table to test with
DROP TABLE #student_marks
CREATE TABLE #student_marks (COURSE_ID VARCHAR(6), MARKS_TYPE VARCHAR(12), MARKS INT, STUDENT_ID INT)
-- put some data in it
INSERT INTO #student_marks (COURSE_ID, MARKS_TYPE, MARKS, STUDENT_ID)
SELECT 'ASM001', 'Mid-term', 23, 3015 UNION ALL
SELECT 'ASM001', 'End-term', 31, 3015 UNION ALL
SELECT 'OB001', 'Mid-Term', 19, 3001 UNION ALL
SELECT 'ASM001', 'Project', 37, 3002
SELECT d.STUDENT_ID, d.COURSE_ID, TotalMarks,
CASE
WHEN d.TotalMarks BETWEEN 100 AND 91 THEN 'A+'
WHEN d.TotalMarks BETWEEN 90 AND 83 THEN 'A'
WHEN d.TotalMarks BETWEEN 75 and 82 then 'B+'
WHEN d.TotalMarks BETWEEN 67 and 74 then 'B'
WHEN d.TotalMarks BETWEEN 59 and 66 then 'C+'
WHEN d.TotalMarks BETWEEN 51 and 58 then 'C'
WHEN d.TotalMarks BETWEEN 43 and 50 then 'D+'
WHEN d.TotalMarks BETWEEN 35 and 42 then 'D'
ELSE 'X' END AS Grade
FROM (
SELECT STUDENT_ID, COURSE_ID, SUM(MARKS) AS TotalMarks
FROM #student_marks
GROUP BY STUDENT_ID, COURSE_ID
) d
Results:
STUDENT_ID COURSE_ID TotalMarks Grade
----------- --------- ----------- -----
3002 ASM001 37 D
3015 ASM001 54 C
3001 OB001 19 X
(3 row(s) affected)
Where do these bits come from?
WHEN 'Deniel of Grade' then 'DN'
WHEN 'Absent' then 'ABS'
WHEN 'With Draw From Course' then 'WFC'
WHEN 'Audit Course' then 'A+'
Are they held in a column of a table somewhere?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 20, 2009 at 10:00 am
Strictly as additional information and NOT as a substitute for Steve Jones' or Chris Morris' code,
but just as a matter of reference for yourself,
there is a known bug in SQL 2000 when using subqueries it is at:
February 20, 2009 at 10:06 am
Thanks Bitbucket, that one's new to me! I assumed that the error was from this..
WHEN 'Deniel of Grade' then 'DN'
WHEN 'Absent' then 'ABS'
WHEN 'With Draw From Course' then 'WFC'
WHEN 'Audit Course' then 'A+'
but I suppose it will just throw a syntax error, not the one the OP reported.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 20, 2009 at 10:50 am
As you, I tried with simple code and it worked, I modified it little bit:
select Student, course, credit, sum(gradeno) as Grades
, case
when sum(gradeno) >= 170 then 'A+'
when sum(gradeno) BETWEEN 150 and 169 then 'A'
else 'F'
end 'Grade'
, case
when sum(gradeno) >= 170 then '8'
when sum(gradeno) BETWEEN 150 and 169 then '7'
else '0'
end 'Points'
, Points * credit as Quality
from MyTable
group by Student, course, credit
go
I want the BOLD PART also, but am getting an error, how to solve it???
Am getting following error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Points'.
February 20, 2009 at 11:36 am
As far as I can determine there is NOT a column in your table with the name of Points.
If there is such a column please post the Create Table statement.
Carefully analyze Steve Jones' code and notice that he does NOT include in the select statement
the name of the value given to the result of his case statement
I believe you can obtain what you desire by modifying a segment of your code as shown below.
, case
when sum(gradeno) >= 170 then 8*CREDIT
when sum(gradeno) BETWEEN 150 and 169 then 7*CREDIT
else '0'
end 'Quality'
from MyTable
group by Student, credit
go
February 20, 2009 at 1:01 pm
Sir/Madam,
Am declaring another column in student_marks table named as remark
Now, if in remark, it's given ABS/DN/WFC and sum of marks is 0, then in grade column, it will shown as ABS/DN/WFC
My query is like this:
SELECT dbo.student_marks.student_ID, dbo.student_marks.course_code, dbo.course_details.course_credit, SUM(dbo.student_marks.marks)
AS [Total Marks]
, case
when sum(dbo.student_marks.marks) BETWEEN 91 and 100 then 'A+'
when sum(dbo.student_marks.marks) BETWEEN 83 and 90 then 'A'
when sum(dbo.student_marks.marks) BETWEEN 75 and 82 then 'B+'
when sum(dbo.student_marks.marks) BETWEEN 67 and 74 then 'B'
when sum(dbo.student_marks.marks) BETWEEN 59 and 66 then 'C+'
when sum(dbo.student_marks.marks) BETWEEN 51 and 58 then 'A'
when sum(dbo.student_marks.marks) BETWEEN 43 and 50 then 'A'
when sum(dbo.student_marks.marks) BETWEEN 35 and 42 then 'A'
when sum(dbo.student_marks.marks) = 0 and dbo.student_marks.remark = 'ABS' then 'ABS'
when sum(dbo.student_marks.marks) = 0 and dbo.student_marks.remark = 'WFC' then 'WFC'
when sum(dbo.student_marks.marks) = 0 and dbo.student_marks.remark = 'DN' then 'DN'
when sum(dbo.student_marks.marks) BETWEEN 0 and 34 then 'F'
else 'NULL'
end 'Grade'
, case
when sum(dbo.student_marks.marks) BETWEEN 91 and 100 then '8'
when sum(dbo.student_marks.marks) BETWEEN 83 and 90 then '7'
when sum(dbo.student_marks.marks) BETWEEN 75 and 82 then '6'
when sum(dbo.student_marks.marks) BETWEEN 67 and 74 then '5'
when sum(dbo.student_marks.marks) BETWEEN 59 and 66 then '4'
when sum(dbo.student_marks.marks) BETWEEN 51 and 58 then '3'
when sum(dbo.student_marks.marks) BETWEEN 43 and 50 then '2'
when sum(dbo.student_marks.marks) BETWEEN 35 and 42 then '1'
when sum(dbo.student_marks.marks) = 0 and dbo.student_marks.remark = 'ABS' then '0'
when sum(dbo.student_marks.marks) = 0 and dbo.student_marks.remark = 'WFC' then '0'
when sum(dbo.student_marks.marks) = 0 and dbo.student_marks.remark = 'DN' then '0'
when sum(dbo.student_marks.marks) BETWEEN 0 and 34 then '0'
else 'NULL'
end 'Points'
FROM dbo.student_marks INNER JOIN
dbo.course_details ON dbo.student_marks.course_code = dbo.course_details.course_code
GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code, dbo.course_details.course_credit, dbo.student_marks.remark
But am getting a error i.e
If Data in Student_Marks is
-------------------------------------------------
student_ID course_code marks_type marks remarks
----------- ----------- ----------- ------ -------
3015 ASM001 End-Term 0
3015 OBI Mid-Term 10
3015 ASM001 Mid-Term 0 ABS
3015 ASM001 Project 59
-------------------------------------------------
Then result giving:
-------------------------------------------------------
student_ID course_code credit Total Marks Grade Points
---------- ----------- ------ ---------- ------ ------
3015 ASM001 3 59.0 C+ 4
3015 ASM001 3 0.0 ABS 0
3015 OBI 3 10.0 F 0
-------------------------------------------------------
Same way, If I input data ZERO in this form:
-------------------------------------------------
student_ID course_code marks_type marks remarks
----------- ----------- ----------- ------ -------
3015 ASM001 End-Term0
3015 OBI Mid-Term 10
3015 ASM001 Mid-Term0 ABS
3015 ASM001 Project 0
-------------------------------------------------
Then result giving:
-------------------------------------------------------
student_ID course_code credit Total Marks Grade Points
---------- ----------- ------ ---------- ------ ------
3015 ASM001 3 0.0 F 0
3015 ASM001 3 0.0 ABS 0
3015 OBI 3 10.0 F 0
-------------------------------------------------------
In this both results, ASM001's output is showing error!!
What should I do??
February 20, 2009 at 1:32 pm
mail2payan (2/20/2009)
What should I do??
You should spend a little time reading the earlier replies from Steve Jones and Bitbucket.
Also, check out "table alias" and "derived table" in BOL.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 20, 2009 at 1:50 pm
Boy...I wish my school gave an 'A' for an 83.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 20, 2009 at 2:13 pm
Sir,
I reading the earlier replies from Steve Jones and Bitbucket, but I also want the 'ABS' part also, is there any other solution to this part.
I modified Steve Jones query and in my modification, it's giving the following error:
create table MyTable
( Student varchar(10), course varchar(10), credit varchar(2)
, gradeno int, remark varchar(10)
)
go
insert MyTable select 'Steve', 'ASM01', 3, 50,0
insert MyTable select 'Steve', 'ASM01', 3, 43,0
insert MyTable select 'Bob', 'ASM01', 3, 0,0
insert MyTable select 'Bob', 'OB01', 3, 23,0
insert MyTable select 'Bob', 'OB01', 3, 59,0
insert MyTable select 'Bob', 'ASM01', 3, 100,0
insert MyTable select 'Andy', 'OB01', 3, 0,0
insert MyTable select 'Andy', 'ASM01', 3, 50,0
insert MyTable select 'Andy', 'ASM01', 3, 10,0
insert MyTable select 'Andy', 'OB01', 3, 70,0
insert MyTable select 'Andy', 'OB01', 3, 0, 'ABS'
go
select Student, course, credit, sum(gradeno) as Grades
, case
when sum(gradeno) BETWEEN 91 and 100 then 'A+'
when sum(gradeno) BETWEEN 83 and 90 then 'A'
when sum(gradeno) BETWEEN 75 and 82 then 'B+'
when sum(gradeno) BETWEEN 67 and 74 then 'B'
when sum(gradeno) BETWEEN 59 and 66 then 'C+'
when sum(gradeno) BETWEEN 51 and 58 then 'A'
when sum(gradeno) BETWEEN 43 and 50 then 'A'
when sum(gradeno) BETWEEN 35 and 42 then 'A'
when sum(gradeno) = 0 and remark = 'ABS' then 'ABS'
when sum(gradeno) BETWEEN 0 and 34 then 'F'
else 'NULL'
end 'Grade'
, case
when sum(gradeno) BETWEEN 91 and 100 then '8'
when sum(gradeno) BETWEEN 83 and 90 then '7'
when sum(gradeno) BETWEEN 75 and 82 then '6'
when sum(gradeno) BETWEEN 67 and 74 then '5'
when sum(gradeno) BETWEEN 59 and 66 then '4'
when sum(gradeno) BETWEEN 51 and 58 then '3'
when sum(gradeno) BETWEEN 43 and 50 then '2'
when sum(gradeno) BETWEEN 35 and 42 then '1'
when sum(gradeno) = 0 and remark = 'ABS' then '0'
when sum(gradeno) BETWEEN 0 and 34 then '0'
else '0'
end 'Points'
, case
when sum(gradeno) BETWEEN 91 and 100 then 8 * credit
when sum(gradeno) BETWEEN 83 and 90 then 7 * credit
when sum(gradeno) BETWEEN 75 and 82 then 6 * credit
when sum(gradeno) BETWEEN 67 and 74 then 5 * credit
when sum(gradeno) BETWEEN 59 and 66 then 4 * credit
when sum(gradeno) BETWEEN 51 and 58 then 3 * credit
when sum(gradeno) BETWEEN 43 and 50 then 2 * credit
when sum(gradeno) BETWEEN 35 and 42 then 1 * credit
when sum(gradeno) = 0 and remark = 'ABS' then '0'
when sum(gradeno) BETWEEN 0 and 34 then '0'
else '0'
end 'Quality'
from MyTable
group by Student, course, credit, remark
go
drop table MyTable
Even, I need to get Total Quality/Total credit as Aggregate.
I need the 'ABS' part and am a student, I have very less experience in this MS SQL, please help me in someway...please!!
February 20, 2009 at 2:50 pm
Please help us to help you...
READ the article in my signature block and post the create table statements, and sample data for each table
after you have done that please explain why this fragment of code is in your T-SQL
when sum(dbo.student_marks.marks) = 0 and dbo.student_marks.remark = 'ABS' then '0'
when sum(dbo.student_marks.marks) = 0 and dbo.student_marks.remark = 'WFC' then '0'
when sum(dbo.student_marks.marks) = 0 and dbo.student_marks.remark = 'DN' then '0'
when sum(dbo.student_marks.marks) BETWEEN 0 and 34 then '0'
Is this a homework assignment or a an answer to a question in a take home examination?
February 20, 2009 at 3:11 pm
Sir,
I had read your signature block and in my last post, I had posted the create table statements, and sample data for each table in my last post.
I had used Steve Jones query to show the error.
This ABS, WFC and DN stands for Absent, Withdraw From Course and Deniel of Grade, respectively.
So, why I need this?
Am giving example of 'Absent'
Sometime, it may happens student is absent during an exam, say in mid-term, as well as absent in end-term and as a result, faculty should mark him absent in mid-term and end-term and give ZERO, so am using ABS for that, whenever this ABS is Given, the student will get ZERO and in point, it will show 0.00 and grade will be ABS.
But, in some cases maybe student absent for mid-term and present for end-term, so he will rewarded for end-term only and grade and point given accordingly.
Hope, now I can make you understand, why I need the ABS, WFC and DN so badly in my T-SQL.
And this is my semester project, where am creating a SCHOOL MANAGEMENT SYSTEM and for that am using MS SQL Server 2000 as backend and ASP 3.0 as frontend. This is just a part of my whole project.
Please help me.
February 20, 2009 at 8:40 pm
Mail2payan
I ran your modification of Steve Jones' T-SQL and reieved the following results:
Student course credit Grades Grade Points Quality
---------- ---------- ------ ----------- ----- ------ -----------
Andy ASM01 3 60 C+ 4 12
Andy OB01 3 70 B 5 15
Andy OB01 3 0 ABS 0 0
Bob ASM01 3 100 A+ 8 24
Bob OB01 3 82 B+ 6 18
Steve ASM01 3 93 A+ 8 24
(6 row(s) affected)
Please point out the error in the results for me.
February 21, 2009 at 1:47 am
Student course credit Grades Grade Points Quality
---------- ---------- ------ ----------- ----- ------ -----------
Andy OB01 3 70 B 5 15
Andy OB01 3 0 ABS 0 0
You can see this Andy part, appearing twice whereas it should come as:
Student course credit Grades Grade Points Quality
---------- ---------- ------ ----------- ----- ------ -----------
Andy OB01 3 70 B 5 15
As he is absent in one part of course evaluation, but he appeared in other part of evaluation and got 70.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply