get all students who has got more marks than the average marks of that class

Question

I got two tables below called tbl_student where we store students' data like id, name, class, roll no. and age, and tbl_marks where we store marks scored in an exam. -

CREATE TABLE IF NOT EXISTS public.tbl_student
(
    student_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    student_name varchar(255) NOT NULL,
    student_class integer,
    student_roll_no integer,
    age integer,
    CONSTRAINT tbl_student_pkey PRIMARY KEY (student_id)
)

and

CREATE TABLE IF NOT EXISTS public.tbl_marks
(
    marks_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    student_id integer NOT NULL,
    subject varchar(255),
    marks integer,
    CONSTRAINT tbl_marks_pkey PRIMARY KEY (marks_id)
)

And these are some data for the respective tables:

INSERT INTO
    public.tbl_student (student_name, student_class, student_roll_no, age)
VALUES
    ('John Doe',5,1, 12),
    ('Clark Keny', 5,2, 10),
    ('Ross Barkley', 5, 3, 11),
    ('Frank Lampard', 5, 4, 9),
    ('John Terry', 5, 5, 10),
    ('Peter Parker',6,1, 13),
    ('Tony Stark', 6,2,11),
    ('Bruce Wayne', 6, 3,14),
    ('Johnny Depp', 6, 4,11),
    ('Jackie Chan',7,1,15),
    ('John Wick', 7,2,14),
    ('Indiana Jones', 7, 3,11),
    ('Halley Berry', 7, 4,12),
    ('Jane Doe', 7, 5,15),
    ('Martha Kent', 7, 6,13);

and

INSERT INTO
    public.tbl_marks (student_id, subject, marks)
VALUES
    (1, 'Math' , 79),
    (1, 'Science' , 64),
    (2, 'Math' , 69),
    (2, 'Science' , 72),
    (3, 'Math' , 30),
    (3, 'Science' , 50),
    (4, 'Math' , 77),
    (4, 'Science' , 72),
    (5, 'Math' , 84),
    (5, 'Science' , 88),
    (6, 'Math' , 36),
    (6, 'Science' , 54),
    (7, 'Math' , 55),
    (7, 'Science' , 54),
    (8, 'Math' , 66),
    (8, 'Science' , 78),
    (9, 'Math' , 43),
    (9, 'Science' , 20),
    (10, 'Math' ,87 ),
    (10, 'Science' , 92),
    (11, 'Math' , 68),
    (11, 'Science' , 82),
    (12, 'Math' , 78),
    (12, 'Science' , 93),
    (13, 'Math' , 20),
    (13, 'Science' , 22),
    (14, 'Math' , 40),
    (14, 'Science' , 42),
    (15, 'Math' , 52),
    (15, 'Science' , 62);

Now, I need to get all students in each class who got more marks than the average marks in their respective classes.

I tried it using the RANK() function with the PARTITION BY clause.

Answer 1

OK, so finding the averages is like this:

    SELECT s.student_class, m.subject, AVG(m.marks) AS average
      FROM tbl_marks m INNER JOIN tbl_student s USING (student_id) 
      GROUP by s.student_class, m.subject

So, we just need to use that as another table in the join:

SELECT DISTINCT s.student_name, s.student_class, m.subject, m.marks
  FROM tbl_marks m 
  INNER JOIN tbl_student s USING (student_id) 
  INNER JOIN (
    SELECT s.student_class, m.subject, AVG(m.marks) AS average
      FROM tbl_marks m INNER JOIN tbl_student s USING (student_id) 
      GROUP by s.student_class, m.subject) x1 
    USING (student_class, subject)
  WHERE m.marks >= x1.average;

Output:

John Doe              5              Math        79        
Clark Keny            5              Math        69        
Clark Keny            5              Science     72        
Frank Lampard         5              Math        77        
Frank Lampard         5              Science     72        
John Terry            5              Math        84        
John Terry            5              Science     88        
Peter Parker          6              Science     54        
Tony Stark            6              Math        55        
Tony Stark            6              Science     54        
Bruce Wayne           6              Math        66        
Bruce Wayne           6              Science     78        
Jackie Chan           7              Math        87        
Jackie Chan           7              Science     92        
John Wick             7              Math        68        
John Wick             7              Science     82        
Indiana Jones         7              Math        78        
Indiana Jones         7              Science     93