Question 183782 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Optimize the query to display the total score of the student

Wrote a query for this small dataset to find the total score of students.

Conditions:

  1. Display student total score (top scorer on the top).
  2. If student gave an exam 2 time than pick the max score.
  3. if student have max score less than 30 in any subject then remove from output .

— create

CREATE TABLE Student (
  id INTEGER PRIMARY KEY auto_increment,
  name TEXT NOT NULL,
  subject TEXT NOT NULL,
  marks int 
);

— insert

INSERT INTO Student (name, subject, marks) VALUES ('Rahul', 'Math', 60);
INSERT INTO Student (name, subject, marks) VALUES ('Rahul', 'Science', 60);
INSERT INTO Student (name, subject, marks) VALUES ('Rahul', 'English', 29);
INSERT INTO Student (name, subject, marks) VALUES ('Rahul', 'English', 37);

INSERT INTO Student (name, subject, marks) VALUES ('Nitin', 'Science', 68);
INSERT INTO Student (name, subject, marks) VALUES ('Nitin', 'English', 69);
INSERT INTO Student (name, subject, marks) VALUES ('Nitin', 'Math', 73);


INSERT INTO Student (name, subject, marks) VALUES ('Naveen', 'Math', 60);
INSERT INTO Student (name, subject, marks) VALUES ('Naveen', 'Science', 20);
INSERT INTO Student (name, subject, marks) VALUES ('Naveen', 'English', 29);
INSERT INTO Student (name, subject, marks) VALUES ('Naveen', 'English', 87);

— fetch

SELECT name, sum(score) as totalScore from (
  SELECT name, subject, max(marks) as score from Student where name not in (
    SELECT name from (
      SELECT name, subject, max(marks) as marks from Student group by name, subject
    ) as abc where marks < 30
  ) group by name, subject
) as f group by name order by totalScore DESC;

–Output

name    totalScore
Nitin   210
Rahul   157

Query mentioned gave correct output but curious to learn if there are any other ways to retrieve the correct output or whether we can optimise this query.

Tags:

3

Answers


  1. 0

    This is an other way to do it using left join :

    First we need to identify students having max score less than 30 in any subject :

    select name, subject, max(marks) as marks
from Student
group by name, subject
having max(marks) < 30

    Then we will have to eliminate those students by performing a left join on this dataset t with a condition t.name is null :

    select s.name, s.subject, max(s.marks) as maxSubjectScore
from Student s
left join (
  select name, subject, max(marks) as marks
  from Student
  group by name, subject
  having max(marks) < 30
) as t on s.name = t.name
where t.name is null
group by s.name, s.subject

    Then we apply the aggregate function sum() to this dataset to get the expected data:

    select name, sum(maxSubjectScore) as totalScore
from (
    select s.name, s.subject, max(s.marks) as maxSubjectScore
    from Student s
    left join (
      select name, subject, max(marks) as marks
      from Student
      group by name, subject
      having max(marks) < 30
    ) as t on s.name = t.name
    where t.name is null
    group by s.name, s.subject
) as s
group by name
order by totalScore DESC;

    Demo here

    Login or Signup to reply.
  2. 0

    You can first find the maximum mark for each subject per Student and then use it to filter any Student who has a score of less than 30 to filter out and calculate the sum.

    With a CTE

    with StudentMarks as (
    -- Select all students with their maximum score for each subject
    select
        name,
        subject,
        max(marks) marks
    from
        Student
    group by
        name,
        subject
)
-- Select all students who have score more than 30 for all subjects
select
    name,
    sum(marks) marks
from
    StudentMarks
group by
    name
having
    min(marks) >= 30
order by
    marks desc;

    Same thing without CTE

    select
    name,
    sum(marks) marks
from
    (
        select
            name,
            subject,
            max(marks) marks
        from
            Student
        group by
            name,
            subject
    ) StudentMarks
group by
    name
having
    min(marks) >= 30
order by
    marks desc;
    Login or Signup to reply.
  3. 0

    A simple solution without joins is to count subjects with lower marks and exclude the student after the aggregation if lower marks count > 0

    SELECT name, SUM(IF(mark >= 30, 0, 1)) AS lower_marks_count, SUM(mark) AS total_mark
FROM (
    SELECT name, subject, MAX(marks) mark
    FROM Student
    GROUP BY name, subject
) t
GROUP BY name
HAVING lower_marks_count = 0
ORDER BY total_mark DESC

    name    total_mark
Nitin   210
Rahul   157

    DEMO

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search