agustincola agustincola - 1 month ago 6
MySQL Question

SQL SUM() the 3 best values of a table

I have a table that looks something like the following :

tablename: user

name points id_subligue ...
------------------------------------------
user1 100 1
user2 200 1
user3 300 1
user4 400 1


when i do this:

SELECT user.name, user.points AS TOTAL
FROM user
WHERE user.id_subligue='1'
ORDER BY user.points DESC
LIMIT 3


Exit:

name TOTAL
------------------
user4 400
user3 300
user2 200


If i do this:

SELECT SUM(user.points) AS TOTAL
FROM user
WHERE user.id_subligue='1'
ORDER BY user.points DESC
LIMIT 3


Exit:

TOTAL
-----
1000


I wish the sum of 3 best user by points! not the sum of all user with id_subligue='1'. So TOTAL=900
Any ideas?
What i am doing wrong? o how can i do it?
Thanx!

Heading


Answer

If you group your query results by users, you will get sums of points per user.

SELECT SUM(user.points) AS TOTAL, user.name
FROM user
WHERE user.id_subligue='1'
GROUP BY user.name
ORDER BY user.points DESC
LIMIT 3