Sum first 20 rows in grouped items

Question

I have a table named iran_ranking like:

user_id	user_state	point_year	user_point	user_sex
1	Tehran	2022	200	men
2	Tehran	2021	200	men
3	Ahwaz	2020	190	men
1	Tehran	2022	180	men
3	Ahwaz	2022	170	men

I want to sum all user_point of user_id like this:

user_id 1 = 380,
user_id 2 = 200,
user_id 3 = 360,

I reach the first goal with this statement:

SELECT *,
SUM(user_point) AS pointsum
from iran_ranking 
where user_sex = 'men' AND point_year > 2019
group by user_id";

Now, I need to get sum of first 20 player of Tehran & Ahwaz like this:

Tehran = 580
Ahwaz = 360

I tried SELECT TOP(20) and gave me error, I also tried LIMIT 20, but it limited first 20 of all rows not first 20 of Tehran and first 20 of Ahwaz.

Answer 1

SELECT *,
       SUM(pointsum) AS pointsum_top20
FROM (
    SELECT user_state,
           SUM(user_point) AS pointsum,
           ROW_NUMBER() OVER (PARTITION BY user_state ORDER BY SUM(user_point) DESC) rn
    FROM iran_ranking 
    -- WHERE ...
    GROUP BY user_id, user_state
    ) subquery
WHERE rn <= 20
GROUP BY user_state