agustincola agustincola - 24 days ago 6
SQL Question

SQL SUM BEST VALUES FROM TABLE AND UPDATE

i wanted to know how can i do this example please:

i have tow tables:

user(id_user, points, id_subligue)

subligue(id_subligue, points)


how can i get the two best user by points and group by id_subligue, sum an add in subligue table.

example:

user
(
user1, 10, 1;
user2, 15, 1;
user3, 20, 1;
user4, 10, 2;
user 5, 20, 2,
user6, 30, 2;
user7, 40, 2
)

subligue
(
1, 35;
2, 70
)


i tried that:

select SUM(user.points) AS TOTAL

from (SELECT user.points
FROM user
ORDER BY user.points DESC
LIMIT 2
) user


but i dont know how to update subligue table with TOTAL and by id_subligue

Sorry if i dont explain very well, i ll answer any questions. Thanks in advance.

Answer

According to my understanding the below query will give you the desire result

Select user.id_user,SUM(subligue.points) AS TOTAL from user inner join subligue on user.id_subligue=subligue.id_subligue
order by subligue.points DESC

//New Solution

select   SUM(user.points) AS TOTAL
from     (Select top 2  user.points from user inner join subligue on user.id_subligue=subligue.id_subligue
order by  user.points DESC ) user

//We can use where clause for getting specific result for id_subligue

select   SUM(user.points) AS TOTAL
from     (Select top 2  user.points from user inner join subligue on user.id_subligue=subligue.id_subligue where user.id_subligue=1 
order by  user.points DESC ) user

As Question is not very clear to me and if this above solution do not working then let me know the final output you want.