My-sql one to many relation join get sum of left table column

Question

When we have two tables with one to many relation how we can get the sum of left table column.

Ex:
Player (player_id,name)
Stats (player_id, game_id, score)

Player
1  Aaron Brooks
2  Beno Udrih
3  James harden

Stats
player_id  score year
1          5     2017
1          3     2017
2          4     2016

The result I need to get is (Player scores in 2017)
Player         Score
Aaron Brooks   8
Beno Udrih     0
James harden   0

One player have many stat records.
Then how can I get all the players list with his sum of total scores. (Even if stats not available for one player his score need to be 0)

Got the correct query

select p.player_id,p.name,coalesce(sum(s.score),0)
from player p
left join stats s on p.player_id = s.player_id and s.year=2017
group by p.player_id,p.name

Answer 1

Apply simple aggregation and use left join on stats table to get all the records from player table whether they have records associated in stats table or not

select p.player_id,p.name,coalesce(sum(s.score),0)
from player p
left join stats s on p.player_id = s.player_id
group by p.player_id,p.name

Demo

Edit for filter

Move your filter for stats table from where clause to on clause

select p.player_id,p.name,coalesce(sum(s.score),0)
from player p
left join stats s on p.player_id = s.player_id and s.year=2017
group by p.player_id,p.name

Applying filter on left table using where clause will convert from left join to inner join

Demo