SQL Server add a score if an answer is the same as the correct answer with group by clause

Question

I have this code and its temporary tables so you can run it.

create table #student(
    id int identity(1,1),
    name varchar(50)
)

create table #quiz(
    id int identity(1,1),
    name varchar(50),
    points_worth int
)

create table #exam(
    id int identity(1,1),
    sequence int,
    question varchar(50),
    answer varchar(50),
    quiz_id int
)

create table #student_taken(
    id int identity(1,1),
    sequence int,
    answer varchar(50),
    student_id int,
    quiz_id int
)

insert into #student(name)
values('Uzumaki Naruto'),('Uchiha Sasuke'),('Haruno Sakura')

insert into #quiz(name,points_worth)
values('Chunin Exam',2)

insert into #exam(sequence,question,answer,quiz_id)
values(1,'Hinata and Neji are siblings','True',1),
(2,'Uchiha Sasuke is part of the Akatsuki','False',1),
(3,'Tsunade and Jiraiya has a son','False',1)

insert into #student_taken(sequence,answer,quiz_id,student_id)
values(1,'True',1,1),(2,'True',1,1),(3,'True',1,1),(1,'True',1,2),(2,'False',1,2),(3,'False',1,2),
(1,'True',1,3),(2,'False',1,3),(3,'False',1,3)

drop table #student
drop table #exam
drop table #quiz
drop table #student_taken 

So as you can see I Uzumaki Naruto only has 1 point, cause he only got 1 correct answer, and both Sakura and Sasuke has 3 points each.

Now I want it to be like this:

  id     name              score
  1      Uzumaki Naruto     2
  2      Uchiha Sasuke      6
  3      Haruno Sakura      6

He got 6 because in my #quiz table i added points worth(it is points worth for each number of items).

So now I was wondering if group by clause is needed to this? and what is the correct summation, I want that if True = True then it adds 1 point and False = False same and False = True will not count.

Here is my attempt

select 
  ST.student_id,
  SUM(1 * Q.points_worth) 'sum'
from #student_taken ST
inner join #exam E
on E.quiz_id = ST.quiz_id
inner join #quiz Q
on Q.id = E.quiz_id
group by ST.student_id

Answer 1

I'm not really sure what your question is here. @JorgeCampos isn't quite correct, in that a GROUP BY is only required if you are returning aggragated and non-aggrated fields in the same dataset (without the use of a OVER clause).

As for getting the result set, I'm not quite sure how you came to the conclusion you did. The value of points_worth is in your quiz table, not the exam table, so I assume every question has the same value for that quiz? If so, this is one idea for your query:

SELECT q.[name] AS QuizName,
       s.[name] As StudentName,
       COUNT(CASE WHEN st.answer = e.answer THEN 1 END) * q.points_worth AS Score,
       COUNT(CASE WHEN st.answer = e.answer THEN 1 END) AS Correct,
       COUNT(CASE WHEN st.answer != e.answer THEN 1 END) AS Incorrect
FROM #student s
     JOIN #student_taken st ON s.id = st.student_id
     JOIN #exam e ON st.[sequence] = e.id
     JOIN #quiz q ON e.quiz_id = q.id
GROUP BY q.[name], s.[name],
         q.points_worth;

We could however, go a little further and see if a Student actually answered all the questions (and exclude those that answered none), thus we get:

INSERT INTO #quiz([name],points_worth)
VALUES('Basic Maths',1);

INSERT INTO #exam([sequence],question,answer,quiz_id)
VALUES(1,'5 + 2 * 3 = 21','False',2),
      (2,'9 - 1 * 2 = 7','True',2);

INSERT INTO #student ([name])
VALUES ('Jane Smith'),('Sally Bloggs');

INSERT INTO #student_taken ([sequence],answer,quiz_id,student_id)
VALUES (1, 'false', 1, 4),
       (1, 'false', 2, 4),
       (2, 'true', 2, 4),
       (1, 'true', 2, 5);
GO

SELECT q.[name] AS QuizName,
       s.[name] As StudentName,
       COUNT(CASE WHEN st.answer = e.answer THEN 1 END) * q.points_worth AS Score,
       COUNT(CASE WHEN st.answer = e.answer THEN 1 END) AS Correct,
       COUNT(CASE WHEN st.answer != e.answer THEN 1 END) AS Incorrect,
       COUNT(CASE WHEN st.answer IS NULL THEN 1 END) AS Unanswered
FROM #quiz q
     JOIN #exam e ON q.id = e.quiz_id
     CROSS JOIN #student s
     LEFT JOIN #student_taken st ON s.id = st.student_id
                                AND e.[sequence] = st.[sequence]
                                AND q.id = st.quiz_id
WHERE EXISTS (SELECT 1 FROM #student_taken sq WHERE sq.student_id = s.id AND sq.quiz_id = q.id)
GROUP BY q.[name], s.[name],
         q.points_worth;

Hope that helps.

Answer 2

You can try this method: find how many correct points / student (query inside of CTE) and then take result and join the #quiz table to calculate the final points by applying the points_worth

;with cte as (
select
    st.student_id
    ,st.quiz_id
    ,COUNT(e.id) as points
from #student_taken st
left join #exam e
    on st.quiz_id = e.quiz_id
    and st.answer = e.answer
    and st.sequence = e.sequence
group by st.student_id, st.quiz_id
) select
    student_id
    ,s.name
    --,quiz_id
    ,points * q.points_worth
from cte
inner join #quiz q
    on quiz_id = q.id
inner join #student s
    on student_id = s.id