Retrieve sum of 5 top score from a table in mysql

Question

I am working on a project where I need to extract the sum of top 5 score_rank for every team sorted in descending order from a table. Here is the table structure

Here is the link of the structure

http://kolkata-web-design.co.in/test/structure.htm

This is the query I am trying

SELECT team_id AS `team` ,  (SELECT SUM(score_rank)
FROM `contest_result_total`
WHERE team = `team_id`
ORDER BY score_rank DESC
LIMIT 5
) AS `score`
FROM `contest_result_total`
GROUP BY team_id
ORDER BY `score` DESC

But it does not give top 5 score instead it calculates the sum of all the contests and not just 5 grouped by team id

Can anyone help me. Thanks

Answer 1

SELECT team_id,sum(score_rank)
FROM contest_result_total
WHERE (SELECT COUNT(*)
      FROM contest_result_total AS c
      WHERE c.team_id = contest_result_total.team_id
        AND contest_result_total.score_rank <= c.score_rank) <= 5
GROUP BY team_id

Answer 2

Try this:

SELECT team_id AS `team` ,  (SELECT SUM(score_rank)
FROM `contest_result_total`
WHERE team = `team_id`
GROUP BY team_id
ORDER BY score_rank DESC
LIMIT 5
) AS `score`
FROM `contest_result_total`
ORDER BY `score` DESC