Aggregate rows with condition in SQL

Question

The Problem

Having a web application where users can solve exams consisting of different types of questions (multiple choice, single choice), I need to select the percentage score for a user submission. I am using PostgreSQL 13.x

Schema

exams (id)
questions (id)
answers (id, question_id, correct)
exam_questions (exam_id, question_id)
exam_answers (user_id, exam_id, answer_id)

What I have

I managed to come up with a query, which selects the data I need:

SELECT a.id, a.question_id, a.correct, (CASE WHEN ea.answer_id::bool THEN TRUE ELSE FALSE END) as selected
     FROM answers a
     FULL OUTER JOIN exam_answers ea
         ON a.id = ea.answer_id AND ea.exam_id = ?
     WHERE a.question_id IN (
         SELECT eq.question_id
         FROM exam_questions eq
         WHERE eq.exam_id = ?
     );

Which returns something like:

|id|question_id|correct|selected|
|--|-----------|-------|--------|
|1 |1          |true   |false   |
|2 |1          |false  |true    |
|3 |1          |false  |false   |
|4 |1          |false  |false   |
|5 |2          |true   |false   |
|6 |2          |true   |true    |
|7 |2          |false  |false   |
|8 |2          |false  |true    |

If possible, how can I modify this SQL to find how many questions have their correct answers completely intersect with the selected ones to get the score?

(1) full join should not be needed assuming the data has proper foreign key references. (2) desired results would make the question clearer. — Gordon Linoff
– Gordon Linoff, Commented Feb 4, 2021 at 14:28
The FULL JOIN doesn't return FULL JOIN result anyway, thanks to the WHERE clause condition. — jarlh
– jarlh, Commented Feb 4, 2021 at 14:31
There might not be an entry for every answer, that’s why I used a FULL JOIN. Am I wrong? — bronikovsky
– bronikovsky, Commented Feb 4, 2021 at 14:41

id'7238 · Accepted Answer · 2021-02-04 16:06:56Z

1

Try the following example:

WITH answers_sheet AS (
  -- < your query > --
)
SELECT
  COUNT(*) AS total_questions,
  SUM((correct = correct_selected) :: int) AS total_correct
FROM (  
  SELECT question_id,
    SUM(correct :: int) AS correct,
    SUM((correct AND selected) :: int) AS correct_selected
  FROM answers_sheet
  GROUP BY question_id
) agg

From your resulting dataset, it will get:

total_questions     total_correct
2                   0

fiddle

In your SQL query it is enough to use the LEFT JOIN instead of the FULL OUTER JOIN. And there is no selection by user.

SELECT a.id, a.question_id, a.correct, 
       (CASE WHEN ea.answer_id::bool THEN TRUE ELSE FALSE END) as selected
FROM exam_questions eq
JOIN answers a ON a.question_id = eq.question_id
LEFT JOIN exam_answers ea 
       ON a.id = ea.answer_id 
      AND ea.user_id = ? -- ???
WHERE eq.exam_id = ?

edited Feb 4, 2021 at 16:06

answered Feb 4, 2021 at 15:41

id'7238

2,7261 gold badge7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Aggregate rows with condition in SQL

The Problem

Schema

What I have

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The Problem

Schema

What I have

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related