insert and if statements to calculate grades

Question

0.00/5 (No votes)

See more:

HI,

Basically I'm creating a student test-software and a part of my table structure is as follows:

RESULTS_TBL: QPaper_ID, Marks, Class, Subject, Grade, Student_ID [THIS TABLE STORES THE CALCULATED RESULTS BASED ON HIS PERFORMANCE]

ANSWERS_TBL: QPaper_ID, Marks, Class, Subject, Student_ID [THIS TABLE STORES ONLY THE CORRECT ANSWERS GIVEN BY THE STUDENTS AND THE MARKS FOR EACH QUESTION ]

I want to calculate the results mainly the Grade and insert the values from ANSWERS_TBL into the RESULTS_TBL . Below is a part of my code:

SQL

DECLARE @A_G INT, @B_G INT, @C_G INT, @D_G INT
SET @A_G = 40
SET @B_G = 30
SET @C_G = 10
INSERT INTO RESULTS ( QPaper_ID, Marks,Student_ID, Class, Grade)
select QPaper_ID, sum(Marks), Student_ID, Class,
case
  when  sum(Marks) > @A_G then 'A'
  when sum(Marks) between @B_G and @A_G then 'B'
  when sum(Marks) between @C_G and @B_G then 'C'
  else  'D'
END
 from ANSWERS where QP_ID = 1000 and Login_ID = 'a'
 GROUP BY QPaper_ID ,Marks, Student_ID, Class

When I run this query, all the rows from ANSWERS are inserted into RESULTS which I don't want. I want only one record which calculates the sum of marks for each question along with the grade obtained. BTW is the code correct.

THANKS

Posted 8-Dec-13 5:18am

Member 10375570

Updated 8-Dec-13 5:21am

v4

Add a Solution

Comments

Member 10375570 8-Dec-13 11:49am

I got what my mistake was. I shouldn't have included Marks in the Group by clause :)

Sergey Alexandrovich Kryukov 8-Dec-13 14:32pm

Is your problem solved?
—SA

Member 10375570 9-Dec-13 9:47am

I just realized that it didn't. Now instead of 4 or more, the same record is inserted twice which is redundant and causing problems? How can I solve this?

coded007 10-Dec-13 2:32am

can you post some sample data for better understanding

Member 10375570 10-Dec-13 19:23pm

The ANSWERS table have the following 4 records for example:
QPaper_ID: 50723 (all 4 records have this same value)
Marks: 4 , 5 , 2 , 9
Student_ID: CA123 (all 4 records have this same value)
Class: 8 (all 4 records have this same value)

As a result of the above query, the RESULTS table looks like this:
QPaper_ID: 50723 . 50723
Marks: 20 , 20
Grade: C , C
Student_ID: CA123 , CA123
Class: 8 , 8

AS you see, the same values are inserted twice.
Please help :(

thatraja 9-Dec-13 7:32am

Share the solution which could help others in future

Maciej Los 9-Dec-13 2:27am

Please, post an answer and mark it as solved (green button).

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2013-12-16T22:35:00

You should break out the select and make it work the way you want, then put it back below an insert statement.

If you want marks per student, you should group by studentid first, I think. If you want to sum marks, you should not group by marks. GRoup by specifies the group that is used to create a group of marks to sum. GRouping by marks, gives you one group for each set of marks, so sum(marks) equals marks for that row.

Member 10375570 · Answer 2 · 2013-12-16T23:28:00

SQL

DECLARE @A_G INT, @B_G INT, @C_G INT, @D_G INT
SET @A_G = 40
SET @B_G = 30
SET @C_G = 10
INSERT INTO RESULTS ( QPaper_ID, Marks,Student_ID, Class, Grade)
select QPaper_ID, sum(Marks), Student_ID, Class,
case
  when  sum(Marks) > @A_G then 'A'
  when sum(Marks) between @B_G and @A_G then 'B'
  when sum(Marks) between @C_G and @B_G then 'C'
  else  'D'
END
 from ANSWERS where QP_ID = 1000 and Login_ID = 'a'
 GROUP BY QPaper_ID , Student_ID, Class

insert and if statements to calculate grades

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0