Mysql – Retrieving the top 5 users with the most views on their posts in total

aggregateMySQL

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:

  1. User1 -> 4
  2. User2 -> 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 "count: 12".

Best Answer

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 .

Related Question