Geek asks and answers

Getting field sum in mysql when choosing maximum values ​​in case of field duplication

I have it

------------------------------------
 course_id  | total marks | year  
------------------------------------
   1              50         1   -------> I want to remove this from the sum               
   2              50         1
   3              50         1  
   1              100        2  
------------------------------------

I really have no idea how to do this,

I have 3 different courses, and I want to get the sum of the final grades, but with an excellent course_id, given the maximum number of years, the result should be 200, thanks in advance

+3
source share
2 answers

You need to use a subquery to determine the maximum year for each course, and then append it to the table again to get grades:

SELECT SUM(`total marks`)
FROM   `table`
  NATURAL JOIN (
    SELECT course_id, MAX(year) AS year
    FROM   `table`
    GROUP BY course_id
  ) AS y

Take a look at sqlfiddle .

+2
source

The easiest way is:

SELECT SUM(m) FROM (
                    SELECT   total_marks AS m
                    FROM     table AS t
                    WHERE    year = (
                                     SELECT MAX(year) 
                                     FROM   table
                                     WHERE  course_id = t.course_id
                                    )
                   ) AS v
+2
source

More articles:


All Articles