Query Question - Group Data

Question

I have a question about how to group the data as follows.

I have a table called ALUNO_LEGADO, as below:

I need to put together a query to get all the students who failed every subject enrolled in a given year. Ex.: Select students who failed all courses in 2015.

• Obs.: the concepts are: A = Approved, R = Failed
• Note. 2: There is no standard number of subjects for each student, as he has the free choice to enroll in 1 or more subjects.

The idea is to select the guy who failed in all the disciplines in which he was related in the year 2015.

---

create table aluno_legado
SELECT 
RA

FROM ALUNO_LEGADO

WHERE conceito = 'R'
AND ano = 2015

GROUP BY RA

However this query simply brings RA’s that have R concept in 2015, and not only students who failed in all courses in 2015. Understands?

Answer 1

Using not exists

In addition to grouping by RA, you must deny if the student has passed any course.

select nome, ra, ano
  from aluno_legado al
 where ano = 2015
   and not exists(select 1
                    from aluno_legado e
                   where e.ra=al.ra
                     and e.ano=al.ano 
                     and e.conceito='A')
 group by ra, nome, ano

See more details on this fiddle.

Explaining: If the sub-select that searches for subjects in which the student has been approved returns some line to clause not exists will fail.

Using having

Another alternative assuming that the column conceito can only have the values A and R would be with the clause having using the function min.

select nome, ra, ano
  from aluno_legado al
 where ano = 2015
 group by ra, nome, ano
having min(conceito) = 'R'

See more details on this fiddle.

Explaining: how A is less than R if the student has passed any of the min will return A causing the comparison to fail.

I put the column name only for easy viewing.