get the sum of related items that contained in one group the returned on condition

Question

this is related to another question get first record in group by result base on condition to make it clear, I'll start from the beginning this is my database structure create database testGroupfirst; go

use testGroupfirst;
go

create table testTbl (

id int primary key identity,name nvarchar(50) ,year int ,degree int , place  nvarchar(50)

)

insert into testTbl values ('jack',2015,50,'giza')
insert into testTbl values ('jack',2016,500,'cai')
insert into testTbl values ('jack',2017,660,'alex')
insert into testTbl values ('jack',2018,666,'giza')
insert into testTbl values ('jack',2011,50,'alex')
insert into testTbl values ('rami',2015,5054,'giza')
insert into testTbl values ('rami',2016,1500,'cai')
insert into testTbl values ('rami',2017,66220,'giza')
insert into testTbl values ('rami',2018,6656,'alex')
insert into testTbl values ('rami',2011,540,'cai')
insert into testTbl values ('jack',2010,50,'cai')
select * from testTbl

this is the result till now

to get the latest 2 orders, etc from a group by this could be solved by this code

SELECT name, year, degree, place
FROM 
(SELECT name,degree, year,  place,
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY degree desc) rn
     FROM testTbl   
) t    
WHERE rn in(1,2,3);

--another way
select t.* 
from testTbl t
    cross apply (select top 2 id from testTbl t2 where t2.name = t.name order by degree desc) r
where t.id = r.id

i need to get aggregate function like sum to get the sum of all related items in one group i make a code like this

select t.*, sum (t.degree) as sumtest 
from testTbl t  
    cross apply (select top 2 id ,degree  , sum (degree) as sumtest from testTbl t2 where t2.place = t.place group by id,degree order by degree  ) r
where t.id = r.id group by t.id,t.name,t.place,t.year,t.degree

but it didn't work like I thought as I need to make aggregate value not scalar for every item itself , i need to get the sum of all items in one group what i need to get is what shown in this pic

Answer 1

Use another window function:

    SELECT name, year, degree, place, sum(degree) over (partition by name) as [sum]
    FROM 
    (SELECT name,degree, year,  place,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY degree desc) rn
         FROM #testTbl   
    ) t  
    WHERE rn in(1,2,3);