CODEWITHSUNDEEP
sqloracle-database
Andres Vista
Andres Vista

Reputation: 23

I need the student with the most failed subjects based only on his last attmept to pass

I have a list of a group of students, their subjects, grades and the amount of tries for each subject. The subjects repeat since some students try multiple times to pass. I need to select the student with most failed subjects but I cant count the repeated fails of a same subject, I need to keep in mind only the last attempt.

+-----------+---------------+-----------+-------------+
| [ name ]  |  [ subject ]  | [ grade ] | [ attempt ] |
+-----------+---------------+-----------+-------------+
| [ Peter ] | [ math ]      | [ 6 ]     | [ 1 ]       |
| [ Peter ] | [ math ]      | [ 7 ]     | [ 2 ]       |
| [Jessica] | [ chemistry ] | [ 8 ]     | [ 1 ]       |
| [Jesicca] | [ spanish ]   | [ 9 ]     | [ 1 ]       |
+-----------+---------------+-----------+-------------+

I tried selecting the failed subjects and group them by alumns but when I also try to include the last attempt, the result is only one subject with the last attempt instead of all the subjects per alumn.

Select 
    a.name, count(am.subject), am.attempt
from
    alumnos a, alumnos_materias am
where 
    a.i_alumn = am.i_alumn and
    (am.attempt = (select max(attempt)
                   from alumn_subject))
group by 
    a.name, am.attempt
having 
    am.i_subject = (select i_subject
                    from  alumn_subject
                    where grade < 7)

I need the student with the most failed subjects, not the most attempts failed.

Upvotes: 1

Views: 104

Answers (2)

LukStorms
LukStorms

Reputation: 29667

How about using a HAVING clause to get those with failed subjects.

Then get the top 1 with most fails from that.

SELECT 
 a.name, 
 COUNT(fails.subject) AS total_failed_subjects
FROM 
(
   SELECT i_alumn, subject
   FROM  alumnos_materias
   GROUP BY i_alumn, subject
   HAVING MAX(grade) <= 6
) fails
JOIN alumnos a ON a.i_alumn = fails.i_alumn
GROUP BY a.i_alumn, a.name
ORDER BY COUNT(fails.subject) DESC
FETCH FIRST 1 ROWS ONLY;

A test on db<>fiddle here

But if you really need the last attempt?
Then you could use ROW_NUMBER to filter on that before counting the fails. 

SELECT 
 a.name, 
 COUNT(fails.subject) AS total_failed_subjects
FROM 
(
   SELECT i_alumn, subject, grade, attempt,
   ROW_NUMBER() OVER (PARTITION BY i_alumn, subject ORDER BY attempt DESC) AS rn
   FROM  alumnos_materias
) fails
JOIN alumnos a ON a.i_alumn = fails.i_alumn
WHERE rn = 1 AND grade <= 6
GROUP BY a.i_alumn, a.name
ORDER BY COUNT(fails.subject) DESC
FETCH FIRST 1 ROWS ONLY;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270021

How about using conditional aggregation? To get the students order by the number of fails 

select a.name,
       count(distinct case when grade < 7 then subject end) as num_failed_subjects
from alumnos a join
     alumnos_materias am
     on a.i_alumn = am.i_alumn
group by a.name
order by num_failed_subjects desc;

Your question is a little vague on what you need:

  • If you need just one student and have a recent version of Oracle, then use fetch first 1 row only.
  • If you have an older version of Oracle, wrap this in a subquery and use where rownum = 1.
  • If you need all students with the same number of fails, wrap this in a subquery and use rank() or dense_rank().

Upvotes: 0

Related Questions