Q&A for linguists, teachers and students of the Korean language
declare @table table(mark int, name varchar(10)) insert @table values(10, 'pete') insert @table values(15, 'pete') insert @table values(10, 'pete') insert @table values(15, 'John') insert @table values(20, 'John') SELECT AVG(mark) OVER(PARTITION BY name),mark, name, ROW_NUMBER() over (PARTITION BY name ORDER BY (select 1)) as number FROM @table