How to add this condition to this query?-CodePudding

I'm working on this query:

SELECT s.studentname,
       Avg(cs.exam_season_one
           cs.exam_season_two
           cs.degree_season_one
           cs.degree_season_two ) / 4 AS average
FROM   courses_student cs
       join students s
         ON s.student_id = cs.student_id
       join SECTION se
         ON s.sectionid = se.sectionid
WHERE  cs.courses_id = 1
       AND ( se.classes_id = 2
              OR se.classes_id = 5 )
       AND s.studentname LIKE 'm%'
GROUP  BY s.studentname

until this moment everything works perfectly but I need to add a last condition and I dont know how. I need to get the sudents with the same average I mean only students with count(average) > 1 (idk if this is right) anyone knows how to solve this problem in this query? PS: I use oracle.

Edit:

The create tables statements and sample data are here: http://sqlfiddle.com/#!4/ebf636

trying to explain the problem more because maybe I did it the wrong way the first time! First, the average is the average of 4 columns The output I expect is to get the names of the students who belong to class 2 or class 5 (classes_id = 2, classes_id = 5), also their name should start with M I want to check their average in a specific course (course_id = 1) and the last condition I'm asking about is that I want to get the students who only have the same average in this course. for example: if we have 4 students and the averages in the course are (60,70,80,80) then I want to get only the last 2 student names because they have the same average. hope it's clear now!

CodePudding user response：

You appear to be asking for the students where the average of the averages of their exam and degree seasons 1 and 2 are greater than 1 for certain sections.

Without sample data and expected output to validate against, its difficult to answer but, for each student, you want to GROUP BY the primary key that uniquely identifies the student (otherwise you may aggregate two students with the same name together) and you only need to check if the section exists (rather than JOINing the section as that would create duplicate rows if there are multiple matching sections and skew the averages).

Then

if we have 4 students and the averages in the course are (60,70,80,80) then I want to get only the last 2 student names because they have the same average

You want to count how many students have the same average and then filter out those with unique averages:

SELECT studentname,
       average
FROM   (
  SELECT a.*,
         COUNT(*) OVER (PARTITION BY average) AS num_with_average
  FROM   (
    SELECT MAX(s.studentname) AS studentname,
           ( Avg(cs.exam_season_one)
               Avg(cs.exam_season_two)
               Avg(cs.degree_season_one)
               Avg(cs.degree_season_two) ) / 4 AS average
    FROM   courses_student cs
           join students s
             ON s.student_id = cs.student_id
    WHERE  cs.courses_id = 1
    AND    s.studentname LIKE 'm%'
    AND    EXISTS(
             SELECT 1
             FROM   section se
             WHERE  s.sectionid = se.sectionid
             AND    se.classes_id IN (2, 5)
           )
    GROUP BY
           s.student_id
  ) a
)
WHERE  num_with_average > 1;

db<>fiddle here