Query to determine the id of the student who has the highest average in Physics, Chemistry and Mathematics

Question

I have table named marks with two columns student_id and marks. student_id column contains a unique id of the student marks column contains marks of the five different subject of the student Separated by comma. I tried multiple ways to determine the id of the student who has the highest average in Physics, Chemistry, and Mathematics. I am not able to get my output. Can someone please help here? Data looks like below: Table - marks

Student Id | marks
101        | 93,63,74,29,57
102        | 63,66,34,89,27
103        | 93,64,54,22,97
104        | 43,93,34,78,87
105        | 95,83,54,49,78

This can be interpreted as first-row student id 101.His/Her Physics marks - 93, Chemistry mark - 63, maths mark - 74, history - 29, philosophy - 57 Below my query that I tried. First, I split the marks column into multiple columns. But I am not able to determine the id of the student who has the highest average in Physics, Chemistry, and Mathematics. I am not able to get my output. Expected Output: Highest mark in physics student id - 105 Highest mark in chemistry student id - 104 Highest mark in mathematics student id - 101

Output should display only the studentid who is having the highest average of first three subject

DELIMITER $$
CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END$$

DELIMITER ;
SELECT student_id,SPLIT_STR(marks, ',', 1) as physics,
     SPLIT_STR(marks, ',', 2) as chemistry,
     SPLIT_STR(marks, ',', 3) as mathematics,
      SPLIT_STR(marks, ',', 4) as history ,
      SPLIT_STR(marks, ',', 5) as philosophy 
FROM   marks;

The "real" answer here is use that function to normalize your data.Put marks into a separate row. — Mihai, Oct 27 '19 at 08:36
select student_id from marks order by substring(marks, 1, 2) + substring(marks, 4, 2) + substring(marks, 7, 2) desc limit 1 — Birju Shah, Jul 19 '20 at 14:01

score 1 · Accepted Answer · answered Oct 27 '19 at 08:10

You can check this below option-

SELECT D.*,E.`Student Id` FROM 
(
    SELECT * FROM
    (
        SELECT Subject,MAX(Marks) Max_Marks FROM
        (
            SELECT `Student Id`, 'physics' Subject,
            SUBSTRING_INDEX(marks, ',', 1) Marks
            FROM your_table A

            UNION ALL

            SELECT `Student Id`, 'chemistry' Subject,
            SUBSTRING_INDEX(REPLACE(marks,concat(SUBSTRING_INDEX(marks, ',', 1),','),''), ',', 1) Marks
            FROM your_table A

            UNION ALL

            SELECT `Student Id`, 'mathematics' Subject,
            SUBSTRING_INDEX(REPLACE(marks,concat(SUBSTRING_INDEX(marks, ',', 2),','),''), ',', 1) Marks
            FROM your_table A
        )B
        GROUP BY Subject
    )C
) D
INNER JOIN 

(
    SELECT `Student Id`, 'physics' Subject,
    SUBSTRING_INDEX(marks, ',', 1) Marks
    FROM your_table A

    UNION ALL

    SELECT `Student Id`, 'chemistry' Subject,
    SUBSTRING_INDEX(REPLACE(marks,concat(SUBSTRING_INDEX(marks, ',', 1),','),''), ',', 1) Marks
    FROM your_table A

    UNION ALL

    SELECT `Student Id`, 'mathematics' Subject,
    SUBSTRING_INDEX(REPLACE(marks,concat(SUBSTRING_INDEX(marks, ',', 2),','),''), ',', 1) Marks
    FROM your_table A
)E ON D.Subject = E.Subject AND D.Max_Marks = E.Marks;

Output is-

chemistry   93  104
mathematics 74  101
physics     95  105

score 0 · Answer 2 · answered Oct 27 '19 at 07:47

Just a suggestion ... If the same arguments mark are ever in the same position
you could obatin the normalized values for the arguments using a subquery

   select studentId
    , SUBSTRING_INDEX(SUBSTRING_INDEX(marks,',',1),",",-1) physics
    ,  SUBSTRING_INDEX(SUBSTRING_INDEX(marks,',',2),",",-1) chemistry
    ,  SUBSTRING_INDEX(SUBSTRING_INDEX(marks,',',3),",",-1) mathematics
    ,  SUBSTRING_INDEX(SUBSTRING_INDEX(marks,',',4),",",-1) history
    ,  SUBSTRING_INDEX(SUBSTRING_INDEX(marks,',',5),",",-1) philosophy
  from marks

and use this subquery result as table for your further analysis

Thank you. It is very helpful. But How to determine the id of the student who has the highest average in Physics, Chemistry, and Mathematics? Can you please suggest? — Bem, Oct 27 '19 at 08:05

Query to determine the id of the student who has the highest average in Physics, Chemistry and Mathematics

2 Answers2