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:
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,
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'
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.