How can I write a query to do this?-CodePudding

I'm working with this database: link.

I want to write a query to get the student name who belongs to class 2 (class_id = 2) and has the highest average, and the highest degree in a specific course(course_id = 9) at the same time, and if there is no student with these data then return nothing.

I managed to write half of it and get the student with the highest average in class 2, but I didn't know how to add the 2nd condition(highest degree in course 9), I tried in many ways it didn't work. This is my query: (sorry for the messy code, I'm still learning)

SELECT s.studentname
FROM   students s
       JOIN section se
         ON s.sectionid = se.sectionid
       JOIN courses_student cs
         ON s.student_id = cs.student_id
WHERE  se.classes_id = 2
GROUP  BY s.studentname
HAVING ( Avg(cs.exam_season_one)
           Avg(cs.exam_season_two)
           Avg(cs.degree_season_one)
           Avg(cs.degree_season_two) ) / 4 = (SELECT
       Max(( Avg(cs.exam_season_one)
               Avg(cs.exam_season_two)
               Avg(cs.degree_season_one)
               Avg(cs.degree_season_two)
           ) / 4)
                                              FROM   students s
                                                     JOIN section se
                                                       ON s.sectionid =
                                                          se.sectionid
                                                     JOIN courses_student cs
                                                       ON s.student_id =
                                                          cs.student_id
                                              WHERE  se.classes_id = 2
                                              GROUP  BY s.studentname)

The way I calculate the average is:

( Avg(cs.exam_season_one)
           Avg(cs.exam_season_two)
           Avg(cs.degree_season_one)
           Avg(cs.degree_season_two) ) / 4

When the way I calculate the degree in a specific course is:

(cs.exam_season_one
           cs.exam_season_two
           cs.degree_season_one
           cs.degree_season_two ) / 4

So how can I edit my query to add the second condition? or is there a better way than this?

PS: I use oracle

CodePudding user response：

You can find the maximum and the average for each student and then use analytic functions to find the greatest for both conditions:

SELECT studentname
FROM   (
  SELECT studentname,
         ROW_NUMBER() OVER (ORDER BY avg_degree DESC) AS rn_avg,
         ROW_NUMBER() OVER (ORDER BY max_degree DESC) AS rn_max
  FROM   (
    SELECT MAX(s.studentname) AS studentname,
           AVG(
             cs.exam_season_one   cs.exam_season_two   cs.degree_season_one   cs.degree_season_two
           ) / 4 AS avg_degree,
           MAX(
             CASE
             WHEN cs.courses_id = 9
             THEN cs.exam_season_one   cs.exam_season_two   cs.degree_season_one   cs.degree_season_two
             END
           ) / 4 AS max_degree
    FROM   students s
           JOIN section se
             ON s.sectionid = se.sectionid
           JOIN courses_student cs
             ON s.student_id = cs.student_id
    WHERE  se.classes_id = 2
    GROUP  BY s.student_id
  )
)
WHERE  rn_avg = 1
AND    rn_max = 1;

Again, don't GROUP BY studentname as you can have two students with the same name.

Which outputs:

STUDENTNAME

maher

STUDENTNAME
maher

sqlfiddle here