select t.player, sum(case when t.eventid = 1 then t.points end) as event1, sum(case when t.eventid = 2 then t.points end) as event2, sum(case when t.eventid = 3 then t.points end) as event3, sum(case when t.eventid = 4 then t.points end) as event4, sum( case when t.points >= any( select best3.points from tbl best3 where best3.player = t.player order by best3.points desc limit 3 ) then t.points end ) from tbl t group by t.player

Output on MySQL 8.0:

Query Error: Error: ER_NOT_SUPPORTED_YET: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Output on Postgres:

| player | event1 | event2 | event3 | event4 | sum | | ------ | ------ | ------ | ------ | ------ | --- | | 1 | 25 | 15 | 20 | 20 | 65 | | 2 | 20 | 13 | 12 | 10 | 45 |

You can even made the code neater on Postgres by using

**FILTER**:

Live test: https://www.db-fiddle.com/f/haMmw4S4f7XMqcBD8CDV7H/1

select t.player, sum(t.points) filter(where t.eventid = 1) as event1, sum(t.points) filter(where t.eventid = 2) as event2, sum(t.points) filter(where t.eventid = 3) as event3, sum(t.points) filter(where t.eventid = 4) as event4, sum(t.points) filter(where t.points >= any( select best3.points from tbl best3 where best3.player = t.player order by best3.points desc limit 3 ) ) as best3 from tbl t group by t.player

Solution on MySQL 8.0, works on Postgres too:

Live test: https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/2

with ranking as ( select player, rank() over(partition by player order by points desc) as xrank, points from tbl ) ,pick3 as ( select player, sum(points) as best3 from ranking where xrank <= 3 group by player ) select t.player, sum(if(t.eventid = 1, t.points,0)) as event1, sum(if(t.eventid = 2, t.points,0)) as event2, sum(if(t.eventid = 3, t.points,0)) as event3, sum(if(t.eventid = 4, t.points,0)) as event4, p.best3 from tbl t join pick3 p on t.player = p.player group by t.player

MySQL 5.7 solution:

Live test: https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/15

select t.player, sum(case when t.eventid = 1 then t.points end) as event1, sum(case when t.eventid = 2 then t.points end) as event2, sum(case when t.eventid = 3 then t.points end) as event3, sum(case when t.eventid = 4 then t.points end) as event4, sum( case when t.points >= ( select best3.points from tbl best3 where best3.player = t.player order by best3.points desc limit 1 offset 2 ) then t.points end ) as best3 from tbl t group by t.player;

Output:

| player | event1 | event2 | event3 | event4 | best3 | | ------ | ------ | ------ | ------ | ------ | ----- | | 1 | 25 | 15 | 20 | 20 | 65 | | 2 | 20 | 13 | 12 | 10 | 45 |