Retrieving the top 5 users with the most views on their posts in total I have 3 tables:

users
posts
visits

Each time a post gets visited, a new visit will be inserted in the following format:
| id | post_id

My post table has:
| id | text | user_id ...

I'm trying to retrieve the TOP 5 users with the most views on their posts in total.
For example:

User1 -&gt; 4
User2 -&gt; 2

SELECT count(v.id) AS count FROM visits AS v RIGHT JOIN pastes AS p ON v.post_id = p.id RIGHT JOIN users AS u ON id = p.id

But all I get is an array with &quot;count: 12&quot;.
nbk has answered already, I have set up your tables in a SQL fiddle, this makes it easier to play around. Consider using this tool for any more questions, because that makes it easier to talk about table structure etc.
http://sqlfiddle.com/#!9/45220f/2
To elaborate more on the question/answer already given: You need to group by a column, so that all columns sharing the same value will be grouped to a single row in the result. Then you can use aggregate functions on this group, like count. There are other aggregate functions like sum (to add numbers in a group). For more information see the documentation at https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html .