SQL - SUM, Distinct and TOP 4
I have a query on a table that I cannot work with.
Below are examples of tables;
Table Members :
|ID|NAME|
|1 |John|
|2 |Joe |
|3 |Paul|
Score Table :
|ID |Score |
|1 | 25 |
|1 | 34 |
|2 | 54 |
|1 | 23 |
|3 | 43 |
|2 | 14 |
|1 | 23 |
|3 | 43 |
|3 | 14 |
|3 | 43 |
|2 | 14 |
|1 | 23 |
|3 | 43 |
|3 | 14 |
I want to display the name of the member and then sum the top 2 ratings and display it with the highest SUM'd rating.
|Paul| 86 |
|Joe | 68 |
|John| 57 |
Thank you in advance
source to share
You can use the window function row_number
to achieve TOP 2 points per ID, then sum it with group by
and finally join the MEMBERS table, something like:
select MEMBERS.name, tt.s from MEMBERS
inner join (
select ID, sum(Score) s from (
select ID, Score, row_number() over(partition by ID order by Score desc) rn from SCORES
) t
where rn <= 2
group by id
)tt
on MEMBERS.id = tt.id
ORDER BY tt.s
source to share
You can use sentence group by
and subtitle
SELECT M.NAME
,(SELECT SUM(S2.SCORE)
FROM (SELECT TOP 2 S1.Score FROM SCORES S1
WHERE S1.ID = S.ID
ORDER BY S1.Score DESC) AS S2
) AS Two_Highest_Scores
FROM SCORES S
LEFT JOIN MEMBERS M
ON M.ID = S.ID
GROUP BY M.ID
,M.NAME
Can you check it out?
source to share
Try this query (requires SQL Server 2012+)
SELECT name, best_score
FROM
(
SELECT id, score
, SUM(score) OVER (PARTITION BY id ORDER BY score DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS best_score
, ROW_NUMBER() OVER (PARTITION BY id ORDER BY score DESC) AS row_num
FROM Scores
) AS t
INNER JOIN Members AS m ON (m.id = t.id AND t.row_num = 1)
ORDER BY best_score DESC;
First, for each identifier, the scores are sorted in descending order.
(PARTITION BY id ORDER BY score DESC)
Then SUM () is used to evaluate the current row and the next 1 row by the ROWS clause
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
Thus, the inner query returns a combined total of 2 points for each row. The first number of lines is then used to get the best_score string. GROUP BY can also be used.
source to share