get average of number of likes in mysql from two tables let's say I have a posts table:

post_id    user_id    text
1             2        abcd
2             1        wert
3             3        yhj


and I have a ranks table:

post_id     liker_user_id      score
1              3                 3
3              4                 1
3              3                 5


I want to get avg(score) for each post so I came up with this simple query:

SELECT posts.post_id, posts.text , avg(ranks.score) as `ranking` FROM posts LEFT JOIN ranks ON ranks.post_id = posts.post_id GROUP BY posts.post_id;


so this is fine but the problem is, it is possible that a post does not have any ranking in the ranks table, so there will be nothing in the ranking field and it gives me something like this:

post_id   text   ranking
1         abcd     3
2         wert     
3         yhj      3


note that there is nothing in ranking for post_id = 2.
I want a zero for such results, like this:

post_id   text   ranking
1         abcd     3
2         wert     0
3         yhj      3


how can I do this in MySQL?
You can use the COALESCE function to return zero if it has no results like so:

SELECT posts.post_id, posts.text , COALESCE(AVG(ranks.score),0) as `ranking` 
  FROM posts 
  JOIN ranks 
    ON ranks.post_id = posts.post_id 
 GROUP BY posts.post_id;