MySQL SUM TOP 2 RECORDS FOR EACH CATEGORY

Question

I have a table for students scores, am trying to sum top 2 marks for all student for a particular category.I have search for similar post but have not gotten correct answer

I have tried summing the marks but am only getting result for two students instead of all students and it does not give me correct value.

SELECT SUM(marks) as totalmarks,stdid 
   FROM (( select marks,stdid 
               from finalresult 
               where `subjectcategory` = 1 
                    AND `classId`='3' AND `year`='2018'  
                    AND `term`='2' AND `type`='23' 
               order by marks  desc 
               LIMIT 2 ))t1  
   GROUP BY stdid

So you want to sum the top 2 marks of each individual student for a given class, term, year and type? So each student should get the sum of the two top rows, and all students should display? — Qirel, May 19 '19 at 07:30

Barbaros Özhan · Answer 1 · 2019-05-19T10:24:38.620

0

An auxiliary subquery might be used for iteration

    SELECT
        stdid, marks
    FROM
    (
        SELECT stdid, marks,
               @rn := IF(@iter = stdid, @rn + 1, 1) AS rn,
               @iter := stdid
         FROM finalresult
         JOIN (SELECT @iter := NULL, @rn := 0) AS q_iter
        WHERE `subjectcategory` = 1 
          AND `classId`='3' 
          AND `year`='2018'  
          AND `term`='2' 
          AND `type`='23'  
        ORDER BY stdid, marks DESC
    ) AS T1
    WHERE rn <= 2

this solution ignores ties and takes only two for each Student ID.

Demo

edited May 19 '19 at 10:24

answered May 19 '19 at 07:53

Barbaros Özhan

59,113
10
31
55

This only give sum for 2 students and i want all students – DUNCAN OSUR May 19 '19 at 07:55

score 0 · Answer 2 · answered May 19 '19 at 12:03

In MySQL 8+, you would do:

SELECT stdid, SUM(marks) as totalmarks
FROM (SELECT fr.*,
             ROW_NUMBER() OVER (PARTITION BY stdid ORDER BY marks DESC) as seqnm
      FROM finalresult fr
      WHERE subjectcategory = 1 AND 
            classId = 3 AND 
            year = 2018 AND  
            term = 2 AND
            type = 23 
     ) fr
WHERE seqnum <= 2
GROUP BY stdid;

Note that I removed the single quotes. Things that look like numbers probably are. And you should not mix type -- put the quotes back if the values really are stored as strings.

In earlier versions, probably the simplest method is to use variables, but you have to be very careful about them. MySQL does not guarantee the order of evaluation of variables in SELECT, so you cannot assign a variable in one expression and use it in another.

A complicated expression solves this. Also, it is best to sort in a subquery (the latest versions of MySQL 5+ require this):

SELECT stdid, SUM(marks) as totalmarks
FROM (SELECT fr.*,
             (@rn := IF(@s = stdid, @rn + 1,
                        IF(@s := stdid, 1, 1)
                       )
             ) as seqnum
      FROM (SELECT fr.*
            FROM finalresult fr
            WHERE subjectcategory = 1 AND 
                  classId = 3 AND 
                  year = 2018 AND  
                  term = 2 AND
                  type = 23 
            ORDER BY stdid, marks DESC
           ) fr CROSS JOIN
           (SELECT @s = '', @rn := 0) params
WHERE seqnum <= 2
GROUP BY stdid;

MySQL SUM TOP 2 RECORDS FOR EACH CATEGORY

2 Answers2