sql: sum of highest n values in each group

Question

I need to find the sum of the n highest values in each group.

With (n=2):

group | points  
g1    |  3  
g2    |  3  
g3    |  4  
g1    |  2  
g1    |  4  
g2    |  5  
g2    |  5  
g3    |  1  
g3    |  2

result

group | sum  
g1    | 7  
g2    | 10  
g3    | 6

sql using join and group

thanks

What version of SQL are you using? Have you tried something yet? — Tim Biegeleisen, Feb 10 '19 at 01:12
Please read [ask] & the voting arrow mouseover texts re researching before considering posting a question. Here you could just google your title with 'site:stackoverflow.com'. — philipxy, Feb 10 '19 at 22:59

score 2 · Answer 1 · edited Feb 10 '19 at 02:18

If your RDBMS supports window function, you can use ROW_NUMBER() to assign a number to each record in the group, ordered by points, and then filter out top 2 records of each group in an outer, aggregated query.

SELECT grp, SUM(points) total
FROM (
    SELECT grp, points, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY points DESC) rn
    FROM mytable
) x 
WHERE rn <= 2
GROUP BY grp
ORDER BY grp

This MySQL 8.0 DB Fiddle with your sample data yields :

| grp | total |
| --- | ----- |
| g1  | 7     |
| g2  | 10    |
| g3  | 6     |

score 0 · Answer 2 · answered Feb 10 '19 at 02:29

Example dataset:

create table #temp (name varchar(20), value int)
insert into #temp values ('g1',2),('g2',2),('g3',2),('g2',7),
                         ('g3',9),('g1',4),('g2',8),('g3',1),('g1',3),('g1',11)

Another way to handle this issue is to use "cross apply" like in Sql Server below:

--This returns top 2 rows for each group where its value is highest.
SELECT x.*
FROM ( SELECT DISTINCT name FROM #temp ) c
    CROSS APPLY ( SELECT TOP 2 * FROM #temp t WHERE c.name = t.name order by value desc ) x

--This returns sum of top 2 value of each group   
SELECT x.name, SUM(x.Value) as Total
FROM ( SELECT DISTINCT name FROM #temp ) c
    CROSS APPLY ( SELECT TOP 2 * FROM #temp t WHERE c.name = t.name order by value desc ) x
group by x.name

Since your @n value here is not static, and will be changed based on user's choice, you can use dynamic query like below:

declare @n int = 2;
declare @sql nvarchar(max) = 'SELECT x.name, SUM(x.Value) as Total
FROM ( SELECT DISTINCT name FROM #temp ) c
       CROSS APPLY ( SELECT TOP '+cast(@n as nvarchar(10))+' * FROM #temp t 
       WHERE c.name = t.name order by value desc ) x
       group by x.name'
exec sp_executesql @sql

sql: sum of highest n values in each group

2 Answers2