Can this be done with a subquery?

user_1479760 0
Viewed 39

I have a table looking like this:

Team    Name     Points
A       Peter    26 
A       John     18
A       Carl     20
A       Robert   32
A       Mike     10
B       Tom      22
B       Michael  28
B       Tina     18
B       Donald   35
B       Jeff     20

I want to get a result from the query that will give me the best 3 users from a team and the SUM of the point from the 3 highest users.

For team A the 3 highest scores are Robert (32), Peter (26) and (Carl (20) which is a total of 78 points. For team B the highest 3 scores are Donald (35), Michael (28) and Tom (22) which is a total of 85 points

So it must be something like this:

Place   Team   Points   
1       B      85
2       A      78

I have tried something like this:

SELECT points, user FROM table ORDER BY points DESC, LIMIT 3

That will give me the 3 users with the highest points but I want also the SUM of these 3 users and these records must be per team.

I think it must be done with a subquery, is this correct?

subquery sum mysql
1 Answers
SELECT team,SUM(points) sp FROM
(
(
SELECT * FROM `table` WHERE team = "A" ORDER BY points  DESC LIMIT 3
)
UNION ALL
(
SELECT * FROM `table` WHERE team = "B" ORDER BY points DESC LIMIT 3 
)
)
AS dbx
GROUP BY team
ORDER BY sp DESC

Explaination:

Your Data:

Sample-data

Get Highest 3 from both Team (A and B). And then, sum After join them All.

Result:

Result

user_56030200
Related