I have a table with information related to students belonging to courses, that take exams with certain levels (knowdledge_level). For each knowledge level, there are several exams (in case of knowledge_level 1, exams are those with ID 1 and 2, and in case of knowledge_level 2, exams are those with ID 3 and 4).
Course Student Exam_ID Knowdledge_level Passed
A 25 1 1 yes
A 25 2 1 yes
A 25 3 2 no
A 25 4 2 yes
A 26 1 1 no
A 26 2 1 no
A 26 3 2 no
A 26 4 2 no
What I'd like to obtain for each student (25 and 26), is the highest level of knowledge for which all exams have been passed. In case of student 25, the result should be 1, because he has passed all the exams of level 1 but failed one exam of level 2, so he could not achieve knowledge level 2 completely, and in case of student 26 the level should be NULL (or zero) because he hasn't passed any exam.
I'd like to know which query should return the desired result, in this case:
Course Student Knowdledge_level
A 25 1
A 26 NULL (or zero)
Could you help me? :)
Thanks!
Use the boolean aggregate function bool_and():
select course, student, max(knowledge_level) as knowledge_level
from (
select
course,
student,
case when bool_and(passed) then knowledge_level else 0 end as knowledge_level
from exams
group by course, student, knowledge_level
) s
group by course, student
order by course, student
course | student | knowledge_level
--------+---------+-----------------
A | 25 | 1
A | 26 | 0
(2 rows)
In this live demo you can see how to build the query in simple steps.